CODEWITHSUNDEEP
sqlms-access
JJJones_3860
JJJones_3860

Reputation: 1542

MS Access 2007 - Update a Field in a Table with data from another Table

Ok - I have spent 2 hours trying to figure this out. Searching StackOverflow, Google, MSDN etc. I am lost.

I have found apparent valid answers for MSSQL and other SQL platforms in here, but they do NOT work for MS Access 2007 SQL.

Table 1 has fields ID and Name, Table 2 has the same fields.

In Table 1 I need to update the Name field with the same field from Table 2, Joined on the ID fields.

I have tried numerous Update queries to no avail.

UPDATE table1
SET table1.Name = table2.Name 
WHERE table1.ID=table2.ID

No worky!

UPDATE table1
SET table1.Name = (
SELECT Name From table2 Where Table2.ID=Table1.ID
)

The dreaded "must use an updateable query" message which I can't figure out a solution to.

UPDATE table1
SET table1.Name=table2.Name
FROM table1 INNER JOIN table2 ON table1.ID = table2.ID

"Syntax error"!

My brain is fried. I swear I have done this numerous times in the past, but can't figure it out. I am sure this is simple and someone has a quick answer.

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

MS Access has slightly different syntax for update with join. You can try:

UPDATE table1 INNER JOIN
       table2
       ON table1.ID = table2.ID
    SET table1.Name = table2.Name;

Upvotes: 2

Related Questions