Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unanswered: Update with subselect

    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:

    update table1 set val = 110;

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •