Re: question on using merge to upsert

From: <>
Date: Mon, 27 Oct 2008 08:18:35 GMT
Message-ID: <vPeNk.4760$>

DA Morgan <> wrote:
> There are working demos of MERGE in Morgan's Library at
> 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.


  • 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

  • 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');

select * from counters;
Mark Harrison
Pixar Animation Studios
Received on Mon Oct 27 2008 - 03:18:35 CDT

Original text of this message