I'm trying to update records based on changes in a another table.

UPDATE TableA
SET TableA.Field = (SELECT TableB.Field FROM TableA INNER JOIN TableB ON TableA.Field_ID=TableB.Field_ID)
WHERE TableA.Field_ID In ("002a","002b","002c");

This is driving me mad, I keep trying to run this and Access 2003 gives me an error message: "Operation must use an updateable query".

Grrrrrr. I've been wrestling with this for some time now, does anyone have any ideas on getting the new TableB.field values to the TableA.Field using the Field_ID as the look up, as well as using the narrowing criteria for those 3 Field_ID's?

I'm not overly good with sub queries but shouldn't this:

(SELECT TableB.Field FROM TableA INNER JOIN TableB ON TableA.Field_ID=TableB.Field_ID)


Be this:

(SELECT TableB.Field FROM TableB INNER JOIN TableB ON TableA.Field_ID=TableB.Field_ID)

And I hope you are just using .Field as an example because that is a reserved word and should not be a field, or object, name. And if it is just an example, why not just use the real thing? We find that using "simplified" code is actually more of a hindrance than a help.

Yes, Bob, those are for Example purposes. I made the switch and still no dice:

UPDATE TableA
SET TableA.Field = (SELECT TableB.Field FROM TableB INNER JOIN TableA ON TableA.Field_ID=TableB.Field_ID)
WHERE TableA.Field_ID In ("002a","002b","002c");

Yes, Bob, those are for Example purposes. I made the switch and still no dice:

UPDATE TableA
SET TableA.Field = (SELECT TableB.Field FROM TableB INNER JOIN TableA ON TableA.Field_ID=TableB.Field_ID)
WHERE TableA.Field_ID In ("002a","002b","002c");

Access might be confused by the dual references to TableA. Try changing the Inner Query to use aliases (SELECT TableB.Field FROM TableA As tblA INNER JOIN TableB As tblB ON tblA.Field_ID = tblB.Field_ID)

I do not believe that the Order of the Tables is a problem. The Order of the Tables in an Inner Join should not matter, as long as the order of the Columns in the ON part of the Join match the order of the Tables.

So I can't get my head around using the inner Join, so I went back to a different format:

UPDATE TableA AS TA SET TA.Name =
(SELECT TB.Name FROM TableB TB WHERE TB.ID = TA.ID)
WHERE TA.ID In ("002a","002b","002c");

I still get the error message: "Operation must use an updateable query"

Does anyone have any idea what's going on?

If I don't change anything, and go to datasheet view, I get all the old records that I need to change, which seems like I'm only 1 step off from getting this where I want it. GAHH!

Anyone know why the inner-select statement isn't "an updateable query"?

Again, I hope that NAME is not the name of your field as that is an Access Reserved Word and will cause issues. If you could, how about using the REAL names of your fields and tables. That might make a bit of a difference instead of going with made up stuff.

Its not, those are aliases

Its not, those are aliases

C'mon, just use the right names that you're using. Obscuring them with aliases isn't helping because for all we know your names might have something to do with your problem. But hey, we can't tell, because you want to withold information. So, let's try this again and this time, post the REAL SQL String you are trying to use.

I thought it was just simpler.

Here's the real code:

UPDATE Service_Client_Activities AS sca
SET sca.Activity_Name =
(SELECT al.Activity_Name
FROM Activity_List AS al
WHERE al.Activity_Code = sca.Activity_Code)
WHERE sca.Activity_Code In ("002a","002b","002c");

I guess its not that bad looking at it here.