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