question on using merge to upsert

From: <>
Date: Sun, 26 Oct 2008 01:13:04 GMT
Message-ID: <AuPMk.6562$>

I would like to use MERGE to upsert some simple values. Here's what I've got, which works fine with all my tests, but MERGE is pretty complicated and I want to make sure I've got my tiny brain around the problem.

Is this a reasonable way to do this? Is there a better way?

Many TIA!

create or replace
procedure ups(xa number)

    merge into mergetest m using dual on (a = xa)

         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);

select * from mergetest;
A                      B                      
---------------------- ---------------------- 
10                     2                      
20                     1                      

Mark Harrison
Pixar Animation Studios
Received on Sat Oct 25 2008 - 20:13:04 CDT

Original text of this message