Re: How to Determine Whether to Insert or Update

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sun, 13 Mar 2005 08:49:19 -0500
Message-ID: <39ium2F5uqhotU1@individual.net>

Kenneth Koenraadt wrote:
> 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
>
>

According to the docs (I'm using 10g) the source can be a subquery:

MERGE
INTO CHILD
USING (SELECT P_ID AS ID, P_ID_ORDERING AS ORDERING           FROM DUAL) AS S
ON (S.ID = CHILD.PARENT_ID
     AND S.ORDERING = CHILD.ORDERING)
WHEN NOT MATCHED THEN UPDATE SET <whatever> WHEN MATCHED THEN INSERT VALUES (...) Am I missing something?

Note that there is nothing in the docs stating the ON clause must refer to the source. FWIW I think the source could be DUAL straight up.

MERGE
INTO CHILD
USING DUAL
ON (P_ID = CHILD.PARENT_ID
     AND P_ORDERING = CHILD.ORDERING)
WHEN NOT MATCHED THEN UPDATE SET <whatever> WHEN MATCHED THEN INSERT VALUES (...) Sometimes I wonder whether USING should be made optional....

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Sun Mar 13 2005 - 07:49:19 CST