Tuesday, January 15, 2008

Upsert / Merge within the Same Table (Oracle)

Okay, I'm a huge fan of using Oracle's %ROWTYPE to define records structures. I love writing code like the following:

DECLARE

   rec_contract  CONTRACT%ROWTYPE := Null;

BEGIN

   -- ... retrieval logic and business logic ...

   -- "Set" logic
   UPDATE CONTRACT
   SET ROW = rec_contract
   WHERE CONTRACT_ID = rec_contract.CONTRACT_ID;

   IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO CONTRACT
      VALUES rec_contract;
   END IF;
END;
Code like the above is really convenient for writing "setters". It can easily be extended to handle DELETE logic too. Now, recently I needed to copy a bunch of data within the same table but with a different primary key. I started to write a bunch of code like the above, but I just needed some of code. To cut to the chase, the code below is an example of how to do an "UPSERT" (like a MERGE) but within the same table [which is impossible with the MERGE command]. Source:
drop table test;
clear;

create table test(
   id number(10),
 name varchar2(50)
);

insert into test(id,name) values (1,'First row');

insert into test
   using select 2,'Row two' from dual;

insert into test
   using select 3,'Row three - unmodified' from dual;

commit;

select * from test;

update test set (id,name) = (select 3,'Row three - modified' from test where id = 3) where id = 3;

commit;

select * from test;
Results:
Table created
 
1 row inserted
 
1 row inserted
 
1 row inserted
 
Commit complete
 
         ID NAME
----------- --------------------------------------------------
          1 First row
          2 Row two
          3 Row three - unmodified
 
1 row updated
 
Commit complete
 
         ID NAME
----------- --------------------------------------------------
          1 First row
          2 Row two
          3 Row three - modified

8 comments:

Anonymous said...

a merge might be possible on the same table if you use dual. e.g. I successfully did the following recently:

merge into tcns_load_batch_ext_source target
using (select p_settlement_id EXT_SETTLEMENT_REF_NO, 'LOKI' EXT_SETTLEMENT_SYSTEM from dual) source
on ( target.EXT_SETTLEMENT_REF_NO = SOURCE.EXT_SETTLEMENT_REF_NO
and target.EXT_SETTLEMENT_SYSTEM = SOURCE.EXT_SETTLEMENT_SYSTEM)
when matched then
update
set load_status = 'ER',
load_status_msg = 'Error!: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' - SP_COPY_STRIP error! ''Pending'' TCNS_LOAD_BATCH_EXT_SOURCE record not found!'
when not matched then
insert (CNS_LOAD_BATCH_EXT_SOURCE_ID, CNS_LOAD_BATCH_ID, EXT_SETTLEMENT_REF_NO, EXT_SETTLEMENT_SYSTEM, LOAD_STATUS, LOAD_STATUS_MSG)
values (sn_tcns_load_batch_ext_source.nextval,p_cns_load_batch_id,p_settlement_id,'LOKI','ER','SP_COPY_STRIP error! ''Pending'' TCNS_LOAD_BATCH_EXT_SOURCE record not found!');

This was a way - in a single statement - for me to upsert based on some parameters coming down the line...

Jason said...

Paul, which version of Oracle are using?

Anonymous said...

> select p_settlement_id EXT_SETTLEMENT_REF_NO, 'LOKI' EXT_SETTLEMENT_SYSTEM from dual

To me it does not make sense to select p_settlement_id from dual.

Did your statement work?

Jason Vogel said...

Anonymous, that's Paul's code so I can't really comment on whether or not it worked.

ciuly said...

p_settlement_id is most likely a parameter, so yes, it works.

Anonymous said...

Pretty nice Paul it works really well..

Anonymous said...

Anonymous, how did you make is work?

I still don't get why select p_settlement_id from dual works.

thanks!

Ullas Sutaria said...

http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table


MERGE INTO Employee USING dual ON ( "id"=2097153 ) WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 2097153,"smith", "john" )