Helmut,
The short answer is update, check whether you actually have updated
something, if not insert - even if you have more rows to insert than
update. There is a paper on the Oriole site (URL below) (named something
like 'SQL Performance : check your algorithms' under the library
section) which gives details and compares, with figures, different ways
to do it. The suggestion above is about twice as fast as your 'close to
pseudo-code' suggestion, which is in turn much faster than inserting and
catching primary key violations.
--
Regards,
Stephane Faroult
email: sfaroult_at_oriolecorp.com
Oriole Corporation
Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------
>
> Hi!
>
> I do have a weird problem to solve. I wanna check if a specific record
> already exists in a table (select * from table where PrimaryKey = 1234). If
> it does, I wanna do an update on that record and if it doesn't, I wanna
> insert a record.
>
> What would be the best (and least expensive) way to achieve this in a
> procedure?
>
> Thanks,
> Helmut
>
> --
> Author: Helmut Daiminger
Received on Wed Sep 13 2000 - 03:50:29 CDT
