CODEWITHSUNDEEP
sqlms-accessjoinupdating
Yaho
Yaho

Reputation: 1

Update a table through another table in Access

I want to update Table1 based on updated information that I have stored in Table2. Where the Table1.User = Table2.User, I want to Update Table1.account to be the value of Table2.account I tried two different SQL queries in Access:

UPDATE Table1
SET Table1.account = Table2.account
FROM Table1
INNER JOIN Table2
ON Table1.User = Table2.User
WHERE Table1.User = Table2.User
;

This one gave me a syntax error, saying that it was missing an operator

UPDATE Table1
SET account = (SELECT account
FROM Table2
WHERE Table1.User = Table2.User);

And this one gave said that the 'Operation must use an updateable query'

I have been working with Access and SQL for not very long, so any suggestions or insights into where I went wrong are very much appreciated.

Upvotes: 0

Views: 46

Answers (1)

cha
cha

Reputation: 10411

MS Access uses a bit different syntax to SQL Server. In your case you need to write:

UPDATE Table1
 INNER JOIN Table2
             ON Table1.User = Table2.User
SET Table1.account = Table2.account;

Upvotes: 1

Related Questions