Re: question on using merge to upsert

DA Morgan <damorgan_at_psoug.org> wrote:
> > 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