Update MSAccess table from another Access table using SQL
27,021
Solution 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.
Solution 2
Try this
UPDATE o
SET o.[Assest Description] = f.[Assest Description]
FROM Original o, Final f WHERE o.[Assest No] = f.[Assest No]
Author by
Hamptonite
Updated on August 08, 2020Comments
-
Hamptonite almost 4 years
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]
-
Barranka over 11 yearsCertainly cmsjr's answer is simpler
-
cmsjr over 11 yearsI'm not totally sure my answer will work with the Access engine, so your answer may be the better one.
-
Hamptonite over 11 yearsThis totally worked and saved my hind quarters. I've tried cmsjr answer, and I thought it was that easy, but it isn't. Thank you both. I'm bad with SQL, but the join statements make me zone out.
-
Barranka over 11 years@user1721535 I'm glad to know that this helped you. Just one more little (and very personal) opinion: MS Access SQL is a very customized SQL dialect (I call it "Bill's SQL")... It's a good place to learn the basics, but it has some tricks that can be confusing
-
Shane Kenyon over 9 yearsBased on this I made the following query in Access 2013 and it worked great:
UPDATE Components INNER JOIN Analysis ON Components.Component = Analysis.Component SETComponents.BusinessUnit = Analysis.[Test Area], Components.Impact = Analysis.Impact, Components.InScope = SWITCH (Analysis.[Test Area] = "NA", False, True, True), Components.InUse = SWITCH (Analysis.[Test Area] = "Not Used", False, True, True)
-
John M about 9 yearsJust as a note -> this query syntax doesn't work for MS Access 2007
-
HansUp almost 9 yearsIt should throw error #3075, "Syntax error (missing operator) in query expression ...". A valid Access SQL
UPDATE
does not include theFROM
keyword, and you must fully define the data source(s) beforeSET
. -
ThisClark almost 9 yearsThis doesn't work for Access 2013 either, same error as pointed out by @HansUp