Re: question on using merge to upsert

From: DA Morgan <>
Date: Sun, 26 Oct 2008 07:06:18 -0700
Message-ID: <> 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 that may help you understand how it works.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Oct 26 2008 - 09:06:18 CDT

Original text of this message