Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

UPDATE statement with INNER JOIN

Posted on 2008-06-10
7
Medium Priority
?
1,644 Views
Last Modified: 2013-11-27
Hi,

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:

                   tbl_A                                                      tlb_B
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:

                   tbl_A                                                  
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.
0
Comment
Question by:angie_angie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 21757467
try like this

UPDATE tbl_A SET tbl_A.fld_a = tbl_B.fld_1, tbl_A.fld_b = tbl_B.fld_2 FROM tlb_A , tbl_B

where  tbl_A.caseID = tbl_B.caseID
0
 

Author Comment

by:angie_angie
ID: 21757490
pratima mcs,

I tried yours. It doesn't seem to work though...
0
 

Author Comment

by:angie_angie
ID: 21757503
error is

Run-time error '3144': Syntax error in UPDATE statement.

Btw, I did it in a Sub by using CurrentDb.Execute sGetSQL and sGetSQL is the UPDATE statement... Could it be the problem?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
frankytee earned 2000 total points
ID: 21757537
UPDATE
tlb_A INNER JOIN tbl_B ON tbl_A.caseID = tbl_B.caseID
SET tbl_A.fld_a = tbl_B.fld_1, tbl_A.fld_b = tbl_B.fld_2
0
 
LVL 22

Expert Comment

by:dportas
ID: 21757565
What is / are the key(s) of tbl_B? If caseID is not unique in tbl_B then the result would be that the two columns get updated from some undetermined row where tbl_A.caseID = tbl_B.caseID. Unpredictable results are not usually desirable and may indicate a flaw in your requirements. Unfortunately you didn't specify a key in tbl_B so we can only guess.
0
 

Author Comment

by:angie_angie
ID: 21757624
dportas,

The caseID in both tables are unique.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21757850
frankytee has posted the correct answer.....
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What we learned in Webroot's webinar on multi-vector protection.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question