Re: PL/SQL Update and Insert into Table using a lookup
From: Svend Jensen <Svend_SPAMKILL__at_OracleCare.Com>
Date: Thu, 15 May 2003 20:24:36 +0200
Message-ID: <3EC3DB64.4080703_at_OracleCare.Com>
>>Example:
>>
>> update table
>> set column=variable
>> where
>> ;
>> exception
>>
>> when no_data_found then
>>
>> tablica:='CELL_PAR_VARS CELL_CS';
>>
>> insert into Table
>> (columns )
>> values (
>> );
>>
>> end;
>>
>>
>>
>>"Mark Regan" <mark.regan_at_bskyb.com> wrote in message
>>news:c00c1cf6.0305150210.290ce007_at_posting.google.com...
>>
>>>Hi,
>>>
>>>I am relatively new to PL/SQL and am trying to create a script that
>>>will update a table with new records from another table or insert the
>>>record if no record currently exists. The records contain a unique
>>>identifier that will be used to check if the record already exists on
>>>the table.
>>>
>>>However the unique identifier does not have an index built on it on
>>>this table.
>>>
>>>Any ideas how I would write this?
>>>
>>>Thanks
>>
>>
>
> Received on Thu May 15 2003 - 20:24:36 CEST
Date: Thu, 15 May 2003 20:24:36 +0200
Message-ID: <3EC3DB64.4080703_at_OracleCare.Com>
[Quoted] [Quoted] In any version, an update of a non-existing row, does not throw an exception. No_data_found exception does not work.
Solution 1: create unique index,
insert; exception, when unique index violation - update.
Solution 2: update where exists (select unique id ....) phase 2: insert where not exists (select unique id ...)
The first is the more elegant and much faster, especialy if collections are used (for all, and/or bulk collect). Gives one execution and all are either inserted or updated.
rgds
/Svend Jensen
Scott Mattes wrote:
> at least in our Oracle 8 an update does not throw an exception, which is why [Quoted] > I suggested trying the insert first (after making the index and I should > have specified a unique index i guess) > > "Robert Dimitrovski" <robert.dimitrovski_at_ht.hr> wrote in message > news:1053006903.395782_at_tuxri2.tkcrij.ht.hr... >
>>Example:
>>
>> update table
>> set column=variable
>> where
>> ;
>> exception
>>
>> when no_data_found then
>>
>> tablica:='CELL_PAR_VARS CELL_CS';
>>
>> insert into Table
>> (columns )
>> values (
>> );
>>
>> end;
>>
>>
>>
>>"Mark Regan" <mark.regan_at_bskyb.com> wrote in message
>>news:c00c1cf6.0305150210.290ce007_at_posting.google.com...
>>
>>>Hi,
>>>
>>>I am relatively new to PL/SQL and am trying to create a script that
>>>will update a table with new records from another table or insert the
>>>record if no record currently exists. The records contain a unique
>>>identifier that will be used to check if the record already exists on
>>>the table.
>>>
>>>However the unique identifier does not have an index built on it on
>>>this table.
>>>
>>>Any ideas how I would write this?
>>>
>>>Thanks
>>
>>
>
> Received on Thu May 15 2003 - 20:24:36 CEST