SQL Update from one table to another

Question

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?

Try UPDATE tbl1 SET userid = (SELECT userid FROM tbl2) – Hans Mar 20 '17 at 18:09 — Hans, Mar 20 '17 at 18:09

score 2 · Answer 1 · answered Mar 20 '17 at 18:13

2

"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];

answered Mar 20 '17 at 18:13

SqlZim

37,248
6
41
59

Hi. Sorry, I was being an idiot. Hans has shown me that I was getting mixed up between an UPDATE and INSERT INTO statement. I should've explained more clearly in my question what I was trying to do. Thanks though – Leroy Mar 20 '17 at 18:25

score 1 · Accepted Answer · answered Mar 20 '17 at 18:12

1

Try

UPDATE tbl1 SET userid = (SELECT userid FROM tbl2)

answered Mar 20 '17 at 18:12

Hans

269
4
14

Hi, thanks for the reply. When I try this I get "Operation must use an updateable query"? Also I would like to be able to amend the Update query to copy over multiple columns once I've got it working – Leroy Mar 20 '17 at 18:15
ok, so do you have data in tbl1 or are you transferering data to tbl1 from tbl2 – Hans Mar 20 '17 at 18:16
There is historical data in tbl1 but I would like to append additional rows onto it with the data from tbl2 – Leroy Mar 20 '17 at 18:17
then use INSERT INTO tbl1 SELECT * FROM tbl2; or INSERT INTO tbl1 (userid) SELECT userid FROM tbl2; – Hans Mar 20 '17 at 18:17
Ahh! Brilliant. That works. Sorry, I realise now that I was getting update and insert into mixed up. Thank you for your help – Leroy Mar 20 '17 at 18:20
please give me a vote if this solved your problems, I always get remarks on misspellings and questions marks and so on... – Hans Mar 20 '17 at 18:22

score 1 · Answer 3 · answered Mar 20 '17 at 18:14

1

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.

answered Mar 20 '17 at 18:14

Aditya Chauhan

104
1
6

SQL Update from one table to another

3 Answers3