Avatar of new_perl_user
new_perl_user
 asked on

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'
Oracle Database

Avatar of undefined
Last Comment
awking00

8/22/2022 - Mon
CarlsbergFTW

Use both conditions separately. You should get the same result!
new_perl_user

ASKER
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.
CarlsbergFTW

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
CarlsbergFTW

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'
new_perl_user

ASKER
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.
new_perl_user

ASKER
sorry ORA -00905
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CarlsbergFTW

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'
new_perl_user

ASKER
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.
CarlsbergFTW

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
CarlsbergFTW

if possible please provide some data to work with. Thanks.
awking00

See attached.
merge.txt
new_perl_user

ASKER
I tried the above one and it is saying missing keyword at and condition.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
awking00

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.