CODEWITHSUNDEEP
sqlms-access
Leroy
Leroy

Reputation: 644

SQL Update from one table to another

I have looked up how to do this and found answers here.

I have set up a basic MS Access 2013 database to test this and have tried both solutions.

When I try to run the below:

UPDATE 
Tbl1 as A
INNER JOIN tbl2 AS B ON A.userID = B.UserID 
SET A.userID = B.[UserID];

I get a message box saying that I am about to update 0 rows. Even though there is UserID data in tbl2.

When I try:

UPDATE A 
SET A.[UserID] = B.[UserID]
FROM tbl1 A, tbl2 B WHERE A.[UserID] = B.[UserID]

I get a "Syntax error (missing operator) in query expression"

I did note that someone mentioned in the comments that the second solution wouldn't work in Access 2013 but like I said, the first solution isn't working either.

Does anyone know where I'm going wrong?

Upvotes: 1

Views: 2535

Answers (3)

Aditya Chauhan
Aditya Chauhan

Reputation: 104

As to what i see here is you are trying to set the values of columns which you are also using in the where clause. While in the answer you quoted, the columns being set is different than those using in the where clause, hence 0 rows update message.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

"I get a message box saying that I am about to update 0 rows. Even though there is UserID data in tbl2."

Because if updating where A.userID = B.[UserID] then A.userID = B.UserID updates 0 rows.

If you were joining on something like a userName then you might be doing something. 

UPDATE Tbl1 as A
INNER JOIN tbl2 AS B ON A.userName = B.userName 
SET A.userID = B.[UserID];

Upvotes: 2

Hans
Hans

Reputation: 269

Try

UPDATE tbl1 SET userid = (SELECT userid FROM tbl2)

Upvotes: 1

Related Questions