Help with a Merge statement in oracle


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'
new_perl_userAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
awking00Connect With a Mentor Commented:
I forgot that you can't update a column used in the on clause. Try this one.
merge.txt
0
 
CarlsbergFTWCommented:
Use both conditions separately. You should get the same result!
0
 
new_perl_userAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CarlsbergFTWCommented:
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
 
CarlsbergFTWCommented:
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
 
new_perl_userAuthor Commented:
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
 
new_perl_userAuthor Commented:
sorry ORA -00905
0
 
CarlsbergFTWCommented:
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
 
new_perl_userAuthor Commented:
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
 
CarlsbergFTWCommented:
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
 
CarlsbergFTWCommented:
if possible please provide some data to work with. Thanks.
0
 
awking00Commented:
See attached.
merge.txt
0
 
new_perl_userAuthor Commented:
I tried the above one and it is saying missing keyword at and condition.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.