Re: upsert strategies?

On Oct 26, 4:10 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> <m..._at_pixar.com> wrote in message
>
> news:JlUMk.3915$D32.1708_at_flpi146.ffdc.sbc.com...
>
>
>
> > So far, I've figured out 4 possible ways to perform upsert-like
> > functionality:
>
> > 1. try insert, on exception update
> > 2. try update, on exception insert
> > 3. delete, then insert
> > 4. merge with DUAL
>
> > It seems so far the nicest solution is #4.
>
> > Here's some questions:
>
> > - Do the other methods have any features which might recommend them?
>
> > - Are there any methods that might be portable to other databases?
>
> > - Are there any methods I might be overlooking?
>
> > Many TIA!
> > Mark
>
> > --
> > Mark Harrison
> > Pixar Animation Studios
>
> #2 won't work.  You will update 0 rows which isn't an error.
> #3 is terrible.  You are generating a lot more redo etc. than you need to.
> Just use the merge statement.  I don't see what dual has to do with it.  You
> should be able to do it in one statement.
> Jim- Hide quoted text -
>
> - Show quoted text -

The issue here is to perform a MERGE using only one table, not two, hence the use of DUAL. Out of all of the listed possibilities the actual MERGE, using DUAL, is your best bet:

merge into employee e
using (select 20 as id from dual) i on (e.id = i.id) when matched then

        update
        set salary = salary + 10000
when not matched then
        insert
        values('BLORPO',20,12000)

You'll not find anything useful in this regard that is portable; then, why do you need it to be? You're better served by writing to the dbms in use, not attempting to generate 'universal' code which likely won't scale nor will it perform.

David Fitzjarrell Received on Tue Oct 28 2008 - 14:19:20 CDT