question on using merge to upsert

From: <mh_at_pixar.com>
Date: Sun, 26 Oct 2008 01:13:04 GMT
Message-ID: <AuPMk.6562$YU2.2380@nlpi066.nbdc.sbc.com>

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!
Mark

create or replace
procedure ups(xa number)
as
begin

    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