Re: question on using merge to upsert
Date: Mon, 27 Oct 2008 08:18:35 GMT
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.
- 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)
- 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 StudiosReceived on Mon Oct 27 2008 - 03:18:35 CDT