?
Solved

Help with a Merge statement in oracle

Posted on 2011-03-08
13
Medium Priority
?
355 Views
Last Modified: 2012-05-11

Hi,
 Below is the merge statement I was using to Update and insert  data into a table. But the problem is  the conditions  in the Update statement are not working. I mean rows are not being updated, but when I remove one of the condition then it works.

MERGE INTO table  a
     USING table b
        ON (b.id = a.id)
WHEN MATCHED
THEN
   UPDATE SET
      a.col1 = b.col1,
      a.col2 = b.col2,
      a.col3 = 13
           WHERE  a.col3 = 6  and
                      b.col4 ='N'
WHEN NOT MATCHED
THEN
   INSERT     (a.col1, a.col2, a.col3)
       VALUES (b.col1, b.col3, 1)
       where b.col4 = 'N'
0
Comment
Question by:new_perl_user
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35070274
Use both conditions separately. You should get the same result!
0
 

Author Comment

by:new_perl_user
ID: 35070364
how can we use them separately, I mean using a seperate update statement. If so then it won't work for me because it has to update only when both conditions are met.
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35070396
Try this please and let me know the result:
MERGE INTO table  a
     USING table b
        ON (b.id = a.id)
WHEN MATCHED
THEN
   UPDATE SET
      a.col1 = b.col1,
      a.col2 = b.col2,
      a.col3 = 13
           WHERE  a.col3 = 6  and
                      b.col4 ='N'
WHEN NOT MATCHED
THEN
   update set     
   a.col1 = b.col1, 
   a.col2 = b.col3,
   a.col3 = 1
   where b.col4 = 'N'

Open in new window

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 3

Expert Comment

by:CarlsbergFTW
ID: 35070430
i believe i might of rushed abit into answering that one!

MERGE INTO table  a
     USING table b
        ON (b.id = a.id)
WHEN MATCHED
THEN
   UPDATE SET
      a.col1 = b.col1,
      a.col2 = b.col2,
      a.col3 = 13
           WHERE  a.col3 = 6  and
                      b.col4 ='N'
WHEN NOT MATCHED
THEN
   INSERT  into a   (a.col1, a.col2, a.col3)
       VALUES (b.col1, b.col3, 1)
       where b.col4 = 'N'
0
 

Author Comment

by:new_perl_user
ID: 35070454
Hi,
 It is saying oracle error ORA-))90% missing keyword near the second update statement.

But when not matched  it should insert because it is a new record.
0
 

Author Comment

by:new_perl_user
ID: 35070458
sorry ORA -00905
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35070481
MERGE INTO table  a
     USING table b
        ON (b.id = a.id)
WHEN MATCHED
THEN
   UPDATE SET
      a.col1 = b.col1,
      a.col2 = b.col2,
      a.col3 = 13
           WHERE  a.col3 = 6  AND
                      b.col4 ='N'
WHEN NOT MATCHED
THEN
   INSERT  VALUES  (b.col1, b.col2, b.col3)
       WHERE b.col4 = 'N'
0
 

Author Comment

by:new_perl_user
ID: 35070533
Insert is working fine, the problem is with  the update conditions

WHERE  a.col3 = 6  AND
                      b.col4 ='N'

Although there are records to be updated  it is not updating.
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35070591
it will only update the columns where the col3 = 6 and col4 = 'N' i hope you're aware about that.
 I'm working blind here and i don't have two tables to work with right now. I'll get home soon and get back to you as soon as i can.
Hopefully you will find the answer meanwhile.
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35070600
if possible please provide some data to work with. Thanks.
0
 
LVL 32

Expert Comment

by:awking00
ID: 35071004
See attached.
merge.txt
0
 

Author Comment

by:new_perl_user
ID: 35072342
I tried the above one and it is saying missing keyword at and condition.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 35072921
I forgot that you can't update a column used in the on clause. Try this one.
merge.txt
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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