I am trying to update values in one table with values in another table but cant seem to get the syntax right. I have created 2 tables table1 and table2 each with a autonumbered primary key named key and a a column (integer) name val, both long integers. I keep getting the error 'operation must use an updateable query'. The database is not read-only neither is the directory or anything else, and I do have permissions to modify the database, and am running several other update statements just fine that do not have a sub-select in them.
statments i tried:
update table1 t1 set t1.val = (select val from table2 t2 where t2.key = t1.key)
update table1 t1 set val = (select value from table2 where key = 1) where key = 1
update table1 t1 set t1.val = (select val from table2 t2 where t2.key = 1) where t1.key = 1
the problem seems to be in the sub-select because the following statement works just fine:
It gives you that error because you're using a value from a select query to update a table...as far as I know you can't do that in Access. You have to create a table of the select query result and then use that in the update query.
Or do it in code by opening a recordset from your select query, assigning the result to a variable, then using the variable in an update command.