[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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'
0
new_perl_user
Asked:
new_perl_user
  • 6
  • 5
  • 2
1 Solution
 
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
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
awking00Commented:
I forgot that you can't update a column used in the on clause. Try this one.
merge.txt
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now