UPDATE statement with INNER JOIN
Posted on 2008-06-10
I encountered an error when trying to use a UPDATE statement with INNER JOIN.
First let me explain to you the task I have. I have two tables tbl_A and tbl_B like this:
caseID fld_a fld_b caseID fld_1 fld_2 fld_3
1 aaa bbb 2 ddd eee fff
2 dd ee 4 ggg hhh iii
I want to INSERT values of caseID, fld_1 and fld_2 from tbl_B into fld_a and fld_b of tbl_A for those caseIDs that tbl_A does not have. However, I want to UPDATE records in tbl_A with values from tbl_B for those caseID that also in tbl_B. So, the result should look like this:
caseID fld_a fld_b
1 aaa bbb
2 ddd eee
4 ggg hhh
Note here caseID in tbl_A is the primary key. No duplicates are allowed.
I can use INSERT INTO statement to insert records. It works fine. But when I tried the following UPDATE statement with INNER JOIN, error occurs.
UPDATE tbl_A SET tbl_A.fld_a = tbl_B.fld_1, tbl_A.fld_b = tbl_B.fld_2 FROM tlb_A INNER JOIN tbl_B ON tbl_A.caseID = tbl_B.caseID
Can anyone tell me where goes wrong? Or do you have any better way to achieve the task that I mentioned?
Thank you very much.