oracle upsert with conditional update

Question

I want to insert a row in a DB based on following codition

if table t has doesnot not have row with key X:
      insert into t mystuff..
else
    if  update t set mystuff... where mykey=X if existingversion < NewVersion

I know that normal merge can be used as follows

MERGE INTO (SELECT * FROM mytable WHERE status='active') old
     USING (SELECT * FROM newtable) new
        ON (new.id = old.id)
      WHEN MATCHED THEN UPDATE SET old.data1=new.data1;

But how do i handle my conditional update of my merge ?

score 0 · Answer 1 · answered Oct 25 '13 at 15:34

You could filter the rows you want to upsert, for instance:

MERGE INTO (SELECT * FROM mytable WHERE status='active') old
     USING (SELECT * 
              FROM newtable n
             WHERE NOT EXISTS (SELECT NULL 
                                 FROM mytable m
                                WHERE m.id = n.id
                                  AND m.version >= n.version) new
        ON (new.id = old.id)
      WHEN MATCHED THEN UPDATE SET old.data1=new.data1;

oracle upsert with conditional update

1 Answers1