Re: question on using merge to upsert

mh_at_pixar.com wrote:
> 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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org