Re: How to Determine Whether to Insert or Update

On Sun, 13 Mar 2005 06:59:37 -0500, Serge Rielau <srielau_at_ca.ibm.com> wrote:

>> Avi.
>take a peek at the MERGE statement.
>
>Cheers
>Serge
>
>--
>Serge Rielau
>DB2 SQL Compiler Development
>IBM Toronto Lab

The MERGE utility is not usable here. It only merges 2 *tables* (in 9i, 10g extends this a bit) , but here you have a set of values to be merged into a table, which MERGE is incapable of handling.

Instead, simply :

begin
  if sql%rowcount = 0 then

• row not existent, insert it insert into child values(in_parentid,........); end if; end;

OR

declare
  DUPLICATE_ROW Exception;
  pragma Exception_init(DUPLICATE_ROW,-1); begin
 insert into child values(in_parentid,........);  exception

• row alreade exists, update instead when DUPLICATE_ROW then update child set ...... where parent_id = in_parentid; end;
  • Kenneth Koenraadt