Re: question on using merge to upsert

DA Morgan <damorgan_at_psoug.org> wrote:
> There are working demos of MERGE in Morgan's Library at www.psoug.org
> that may help you understand how it works.

These were useful once I got the clue from googling around that I could use DUAL. Many thanks for the most excellent Morgan's Library!

Here's a cleaned-up and possibly more understandable example... If you think it's useful, feel free to use this on the MERGE page.

Thanks!
Mark

• Using MERGE to conditionally update or insert into a table.

create table counters(name varchar2(10), value number); create or replace procedure increment_counter(zname varchar2) as
begin

• if the row already exists, update it
• else create a new row merge into counters using dual on (name = zname) when matched then update value=value + 1 when not matched then insert (name, value) values (zname, 1); end; /

call increment_counter('foo');
call increment_counter('foo');
call increment_counter('bar');

-- 
Mark Harrison
Pixar Animation Studios