Oracle Merge To Self

Comments (24) | Share

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.