Update MSAccess table from another Access table using SQL

Question

I am trying to update table Original with the values in Final. I'm a newb 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]

Answer 1

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.

Answer 2

Try this

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