Re: upsert strategies?
Date: Sun, 26 Oct 2008 09:10:03 GMT
Message-ID: <LtWMk.438$225.30@nwrddc02.gnilink.net>
<mh_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
Received on Sun Oct 26 2008 - 04:10:03 CDT