I encountered a problem with Oracle's MERGE DML yesterday.  What I was trying to do is use the "Upsert" feature of the merge, except with no distinct source.  All of the searching I did for the MERGE showed examples of how to merge data from a source into a target.  In my case, I thought the source and the target were the same.

This is what I initially tried.  It didn't work.  It turns out that if the source (s) returns an empty set, then the merge is effectively a no-op

   1: merge into MY_TARGET t
   2: using (select *
   3:          from MY_TARGET
   4:         where COL1 = :p1
   5:           and COL2 = :p2) s
   6:   on (t.COL1 = s.COL1 
   7:   and t.COL2 = s.COL2)
   8: when matched then
   9:   update set t.COL3 = :p3
  10: when not matched then
  11:   insert (COL1, COL2, COL3) 
  12:   values (:p1, :p2, :p3)

I thought for a while to figure out how I could get the source to always return something.  Finally it occurred to me that I could use DUAL.

   1: merge into MY_TARGET t
   2: using (select 1 from DUAL) s
   3:   on (t.COL1 = :p1 
   4:   and t.COL2 = :p2)
   5: when matched then
   6:   update set t.COL3 = :p3
   7: when not matched then
   8:   insert (COL1, COL2, COL3) 
   9:   values (:p1, :p2, :p3)

I hope this helps someone else.  At the very least it will be here the next time I need it.

Tags: , ,
posted on Wednesday, March 5, 2008 12:53 AM
Filed Under [ Oracle Database ]

Comments

Gravatar
# re: Oracle Merge To Self
posted by tytang
on 5/6/2008 8:53 PM
Thanks.. you helped me alot
Gravatar
# re: Oracle Merge To Self
posted by sam
on 6/27/2008 1:05 AM
awesome!!!! i has similar issue, cracked my head for 3 hours, search the web, nothing!!
bam! finally saw your post, tried it, worked like a charm...

Thanks a million.

sam
Gravatar
# re: Oracle Merge To Self
posted by Will Smith
on 6/28/2008 7:58 PM
I'm glad to have helped you guys!
Gravatar
# re: Oracle Merge To Self
posted by Mehmet Özakan
on 1/5/2009 6:05 AM
The case of empty source(s) did not mentioned on the web. I got the same error with you. Your way to do it, is great.

Moreover, when your target and source is the same table, you can just use " using (t.COL1 = :p1 and t.COL2 = :p2) " syntax.

Thanks for sharing your best practices ...
Gravatar
# re: Oracle Merge To Self
posted by Dee
on 1/19/2009 8:35 AM
Thank You. this is exactly what I needed and it was a BIG help to me.
Gravatar
# re: Oracle Merge To Self
posted by gcuevas
on 8/10/2009 8:50 PM
I'm trying to do something similar with bound variables and not having much luck.

MERGE INTO myTarget h
USING (SELECT 1 FROM dual) s
ON (h.H3=:h3)
WHEN MATCHED THEN UPDATE
SET h.H4=:h4, h.H5=:h5
WHEN NOT MATCHED THEN INSERT (h.h3, h.h4, h.h5)
VALUES (:h3,:h4,:h5);

Thoughts?
Gravatar
# re: Oracle Merge To Self
posted by Will
on 8/20/2009 10:26 AM
@gcuevas

What kind of error are you getting?

I can't see any problems with the syntax.
Gravatar
# re: Oracle Merge To Self
posted by summerian
on 9/29/2009 11:08 AM
Thank you very much for this post :)
Gravatar
# re: Oracle Merge To Self
posted by Mahim
on 9/30/2009 3:23 PM
Thanks - I was stuck with this problem and your article helped me out .. thanks for sharing this.
Gravatar
# re: Oracle Merge To Self
posted by aditya
on 10/12/2009 5:35 PM
Thanks. Just what I was looking for!
Gravatar
# re: Oracle Merge To Self
posted by Patrick
on 10/23/2009 9:47 AM
GREAT WORK!!! Oracle documentation sucks big time. I could not find anything like this on the Oracle site.

Thank you for posting!!

Regards,
Patrick
MTR Investors Group
www.MTRIG.com
Gravatar
# re: Oracle Merge To Self
posted by Erik
on 2/22/2010 7:03 AM
Thank you so much for this solution, I've browsed half the internet and still did not find the solution.
Gravatar
# re: Oracle Merge To Self
posted by Sam
on 4/29/2010 4:11 AM
Thanks !
You helped me out...
Gravatar
# re: Oracle Merge To Self
posted by Inderpal Singh
on 7/14/2010 11:08 AM
Thanks a lot. I was totally pissed off trying to find solution to this problem. Thanks a lot again..
Gravatar
# re: Oracle Merge To Self
on 10/5/2010 9:56 AM
Thanks a lot!! I was close to collapse :)
Gravatar
# re: Oracle Merge To Self
posted by Cornal
on 11/19/2010 1:35 PM
Thanks, very useful!
Gravatar
# re: Oracle Merge To Self
posted by Nagh
on 1/11/2011 8:14 AM
you could just wrote
USING MY_TARGET t
Gravatar
# re: Oracle Merge To Self
posted by hayata
on 3/27/2011 12:35 PM
thankss... work perfectly.
use src and target on the same table would render ORA-30926
Gravatar
# re: Oracle Merge To Self
posted by mohamed
on 5/17/2011 8:56 AM
Thanks a lot, this is very helpful
Gravatar
# re: Oracle Merge To Self
posted by Sid
on 7/30/2011 11:17 AM
This should work too,

merge into MY_TARGET t
using DUAL
on (t.COL1 = :p1 and
t.COL2 = :p2)
when matched then
update set t.COL3 = :p3
when not matched then
insert (COL1, COL2, COL3)
values (:p1, :p2, :p3)
Gravatar
# re: Oracle Merge To Self
posted by Evan
on 2/5/2012 11:34 PM
It's a bad idea to use 'using DUAL' instead of 'using (select1 from dual)' concerning the execution performance
Gravatar
# re: Oracle Merge To Self
posted by Tony
on 3/15/2012 4:25 PM
Awesome, worked perfectly for what I needed
Gravatar
# re: Oracle Merge To Self
posted by dc
on 3/22/2012 4:59 PM
Good job!
Gravatar
# re: Oracle Merge To Self
posted by Sathya
on 4/16/2012 3:41 PM
The self merge query helped me to solve my issue very quickly. Thank you so much.
Gravatar
# re: Oracle Merge To Self
posted by Jaya
on 7/9/2012 9:12 AM
I tried the above post, but i get exception "Invalid length for statement". Can somebody help me.
Gravatar
# re: Oracle Merge To Self
posted by Jayendra
on 9/18/2012 8:12 AM
Thanks, nice post!
I had managed with a work around, of first updating and then inserting by checking SQL%ROWCOUNT = 0; but using dual it just takes one merge stmt..gr8.. keep it up !
Gravatar
# re: Oracle Merge To Self
posted by Raj
on 9/28/2012 1:39 AM
Thanks mate it helped me alot , Infact u saved my job, My entire architecture is based on this , beacause i am using wizard navigating from one screen to another and coming back to the screen
Gravatar
# re: Oracle Merge To Self
posted by Alex
on 10/9/2012 10:31 AM
Thnx much.
Gravatar
# re: Oracle Merge To Self
posted by Anu
on 12/7/2012 1:02 AM
Thanks a lot...Worked for my requirement too :)
Gravatar
# re: Oracle Merge To Self
posted by atom
on 1/31/2013 11:46 PM
thanks a lot,ur post was really helpful
Gravatar
# re: Oracle Merge To Self
posted by Learner
on 1/9/2014 4:35 AM
Thanks a lot for the post!! hurray!!
Gravatar
# re: Oracle Merge To Self
posted by OR3ls
on 5/23/2014 8:15 AM
Brilliant thanks!

i was selecting from same table in USING & it really stressed me out ! :)
Gravatar
# re: Oracle Merge To Self
posted by Chaitanya
on 6/6/2014 8:12 AM
You just cant comprehend how much relieved i have been on seeing this post and solution. Thanks a ton.
Gravatar
# re: Oracle Merge To Self
posted by Sumit
on 7/18/2014 6:27 AM
Thanks buddy,,this help me a lot.
I was facing the same prob for target = soruce table upsert .
Gravatar
# re: Oracle Merge To Self
posted by Gaurav Kumar
on 10/15/2015 7:03 AM
Thank you, It was very helpful. :)
Gravatar
# re: Oracle Merge To Self
posted by harishrad
on 12/2/2015 7:09 AM
this is awesome, stumbled upon this solution and solved my issue :)
Gravatar
# re: Oracle Merge To Self
posted by Hardie
on 12/7/2015 2:55 AM
Awesome!! had same issue. Checked internet for hours,but nothing.
Finally came across your post,and boom ! thats's exactly what I needed :)
Thanks a ton !
Gravatar
# re: Oracle Merge To Self
posted by Carlos Ming
on 4/1/2016 4:55 AM
Thanks a lot, the same was happening to me, all examples where from one table to another. This made my day!!
/cheers!
Gravatar
# re: Oracle Merge To Self
posted by Carlos Ming
on 4/1/2016 5:06 AM
Thanks a lot! That made my day!
/cheers

Post A Comment
Title:
Name:
Email:
Comment:
Verification: