DA Morgan <damorgan (AT) psoug (DOT) org> wrote:
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');
select * from counters;

--
Mark Harrison
Pixar Animation Studios

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:
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');
select * from counters;

--
Mark Harrison
Pixar Animation Studios

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:
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');
select * from counters;

--
Mark Harrison
Pixar Animation Studios