question on using merge to upsert

#1

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

#2

mh (AT) pixar (DOT) com wrote:

Quote:

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

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

I,forone, am sitting here looking at "using dual" and wondering
what you are actually trying to accomplish that requires merge.

There are working demos of MERGE in Morgan's Library at www.psoug.org
that may help you understand how it works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

mh (AT) pixar (DOT) com wrote:

Quote:

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

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

I,forone, am sitting here looking at "using dual" and wondering
what you are actually trying to accomplish that requires merge.

There are working demos of MERGE in Morgan's Library at www.psoug.org
that may help you understand how it works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

mh (AT) pixar (DOT) com wrote:

Quote:

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

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

I,forone, am sitting here looking at "using dual" and wondering
what you are actually trying to accomplish that requires merge.

There are working demos of MERGE in Morgan's Library at www.psoug.org
that may help you understand how it works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

mh (AT) pixar (DOT) com wrote:

Quote:

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

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

I,forone, am sitting here looking at "using dual" and wondering
what you are actually trying to accomplish that requires merge.

There are working demos of MERGE in Morgan's Library at www.psoug.org
that may help you understand how it works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

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;

A B
---------------------- ----------------------
10 2
20 1

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

Suppose I have a table of counters keyed by a name:

name | value
-------+-------
foo | 12
bar | 45

and a stored procedure:

bump(name varchar2)

The logic is:

if the name exists in the table, add one to the value
if the name does not exist in the table, insert a row with the name
and set value = 1.

So far, the clearest expression of this I've found seems to
be in mysql, which would be something like this

insert into counters(name,value) values('foo',1)
on duplicate key update value=value+1;

I mentioned DUAL because MERGE is not the obvious choice if you
are working with one table, and it's a bit of a hack (in the
good sense of the word) to use DUAL as the second table.

Thanks!
Mark.

--
Mark Harrison
Pixar Animation Studios

#7

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

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;

A B
---------------------- ----------------------
10 2
20 1

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

Suppose I have a table of counters keyed by a name:

name | value
-------+-------
foo | 12
bar | 45

and a stored procedure:

bump(name varchar2)

The logic is:

if the name exists in the table, add one to the value
if the name does not exist in the table, insert a row with the name
and set value = 1.

So far, the clearest expression of this I've found seems to
be in mysql, which would be something like this

insert into counters(name,value) values('foo',1)
on duplicate key update value=value+1;

I mentioned DUAL because MERGE is not the obvious choice if you
are working with one table, and it's a bit of a hack (in the
good sense of the word) to use DUAL as the second table.

Thanks!
Mark.

--
Mark Harrison
Pixar Animation Studios

#8

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

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;

A B
---------------------- ----------------------
10 2
20 1

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

Suppose I have a table of counters keyed by a name:

name | value
-------+-------
foo | 12
bar | 45

and a stored procedure:

bump(name varchar2)

The logic is:

if the name exists in the table, add one to the value
if the name does not exist in the table, insert a row with the name
and set value = 1.

So far, the clearest expression of this I've found seems to
be in mysql, which would be something like this

insert into counters(name,value) values('foo',1)
on duplicate key update value=value+1;

I mentioned DUAL because MERGE is not the obvious choice if you
are working with one table, and it's a bit of a hack (in the
good sense of the word) to use DUAL as the second table.

Thanks!
Mark.

--
Mark Harrison
Pixar Animation Studios

#9

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

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;

A B
---------------------- ----------------------
10 2
20 1

Without you stating the business rules how can we know if what
your statement is doing corresponds with your goal?

Suppose I have a table of counters keyed by a name:

name | value
-------+-------
foo | 12
bar | 45

and a stored procedure:

bump(name varchar2)

The logic is:

if the name exists in the table, add one to the value
if the name does not exist in the table, insert a row with the name
and set value = 1.

So far, the clearest expression of this I've found seems to
be in mysql, which would be something like this

insert into counters(name,value) values('foo',1)
on duplicate key update value=value+1;

I mentioned DUAL because MERGE is not the obvious choice if you
are working with one table, and it's a bit of a hack (in the
good sense of the word) to use DUAL as the second table.

Thanks!
Mark.

--
Mark Harrison
Pixar Animation Studios

#10

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Quote:

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');
select * from counters;

--
Mark Harrison
Pixar Animation Studios