How to Determine Whether to Insert or Update

From: Taysha <eatme_at_joes.place>
Date: Sun, 13 Mar 2005 13:41:28 +0200
Message-ID: <4234275e$1@news.012.net.il>

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. Received on Sun Mar 13 2005 - 05:41:28 CST