Update Microsoft Access table from another Access table using SQL

Question

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]

Sorry for the crappy title. SO was saying couldn't post because title existed :-) — Hamptonite
– Hamptonite, Commented Oct 4, 2012 at 22:58

Barranka · Accepted Answer · 2012-10-04 23:12:14Z

9

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.

edited Oct 4, 2012 at 23:12

answered Oct 4, 2012 at 23:07

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Barranka Over a year ago

Certainly cmsjr's answer is simpler

cmsjr Over a year ago

I'm not totally sure my answer will work with the Access engine, so your answer may be the better one.

Hamptonite Over a year ago

This 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 a year ago

@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 a year ago

Based 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)

cmsjr · Accepted Answer · 2012-10-04 23:02:07Z

2

Try this

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

answered Oct 4, 2012 at 23:02

cmsjr

59.6k11 gold badges76 silver badges63 bronze badges

3 Comments

John M Over a year ago

Just as a note -> this query syntax doesn't work for MS Access 2007

HansUp Over a year ago

It should throw error #3075, "Syntax error (missing operator) in query expression ...". A valid Access SQL UPDATE does not include the FROM keyword, and you must fully define the data source(s) before SET.

ThisClark Over a year ago

This doesn't work for Access 2013 either, same error as pointed out by @HansUp

Peter Mortensen · Accepted Answer · 2023-01-09 02:23:55Z

0

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.

edited Jan 9, 2023 at 2:23

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Oct 20, 2019 at 16:38

billpennock

4601 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

Update Microsoft Access table from another Access table using SQL

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related