CODEWITHSUNDEEP
sqlms-accessms-access-2007
Hamptonite
Hamptonite

Reputation: 109

Update Microsoft Access table from another Access table using SQL

I am trying to update table Original with the values in Final. I'm new to SQL, but I have been at this for two hours trying to change various samples to fit my needs. I am using Access 2007.

UPDATE
  Original o
SET
  o.[Assest Description] = (
    SELECT f.[Assest Description] FROM Original o, Final f
    WHERE o.[Assest No] = f.[Assest No])
WHERE o.[Assest No] = Final.[Asset No]

Upvotes: 4

Views: 28921

Answers (3)

billpennock
billpennock

Reputation: 460

Access is horribly picky. If you fully qualify the name of the field you are updating it may fails. If you don't fully qualify joins tables and fields, it may come back with "join not supported". I say may because it's likely slightly different versions will do something different. At least that's my experience.

So my answer is start with Create → Query Design. Pick the two tables and get the select working correctly. Then modify it to an update query and make the update to of the column(s) you want to update the fields from the table you want to update from. Then switch to SQL view and use that as the model to build your query in VBA.

I had a syntactically correct SQL statement, but until I did this and then matched the qualifying vs. non-qualified field names exactly, it claimed join is not supported.

Upvotes: 0

Barranka
Barranka

Reputation: 21067

I'm not sure your select statement returns only one row. If you want to perform an update on a table using a select statement for assignment, you must be sure that it returns only one row.

Besides that, you may consider the next solution:

update 
   Original as o
   inner join Final as f on o.[Assest No] = f.[Assest No]
set
   o.[Assest Description] = f.[Assest Description]

Notice that this will only work correctly if both [Assest no] is a unique key in both Original and Final tables, and they are properly related.

Upvotes: 9

cmsjr
cmsjr

Reputation: 59235

Try this

UPDATE o 
SET o.[Assest Description] =  f.[Assest Description]
FROM Original o, Final f WHERE o.[Assest No] = f.[Assest No]

Upvotes: 2

Related Questions