Re: PL/SQL Update and Insert into Table using a lookup

We realy don't have any problems with our package when we run it with exceptions.
But, I must say your idea is much better then mine. Regards
Robert
"Svend Jensen" <Svend_SPAMKILL__at_OracleCare.Com> wrote in message news:3EC3DB64.4080703_at_OracleCare.Com...
> 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
> > 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 Fri May 16 2003 - 13:56:32 CEST