Re: How to Determine Whether to Insert or Update

Taysha wrote:
> Folks,
> First some background, then my question.
>
> I am using Oracle 9i (9.2.0.4) on SUN [sparc] Solaris 9
>
> I have a (parent) table:
>
> create table PARENT (ID number primary key);
>
> And a child table:
>
> create table CHILD (PARENT_ID number, ORDERING number,
> primary key (PARENT_ID, ORDERING),
> foreign key PARENT_ID references PARENT (ID))
>
> I have a PL/SQL procedure that updates the parent table, and the child
> table. The procedure receives a number of values that are used to locate
> and update a single row in table PARENT as well as any associated rows in
> table CHILD. If a row doesn't exist in table CHILD, a new row is created
> and if the row does exist, it is updated.
>
> There is a one-to-many relationship between PARENT and CHILD. In other
> words, there may be zero, one or several (but never more than three)
> rows in table CHILD with the same PARENT_ID column value.
>
> I am looking for the most efficient way of determine whether I need to
> insert into or update table CHILD. Currently (and this is code I
> inherited -- I didn't write it originally) I have this:
> [NOTE: This is a very simplified version of my code, for illustration
> only.]
>
> procedure P (P_ID number, P_ORDERING number) is
> L_COUNT number;
> begin
> select count(1) into L_COUNT
> from CHILD
> where PARENT_ID = P_ID
> and ORDERING = P_ORDERING
> and ROWNUM < 2;
>
> if L_COUNT > 0 then
> update CHILD set -- whatever
> where PARENT_ID = P_ID and ORDERING = P_ORDERING;
> else
> insert into CHILD (PARENT_ID, ORDERING)
> values (P_ID, ORDERING);
> end if;
> end;
>
> Thanks (in advance -- for any help),
> Avi.

take a peek at the MERGE statement.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab