Re: Updating a table

In article <slrn6k93c9.kf0.vikasa_at_8080b0da.ims.att.com>, vikasa_at_att.com (Vikas Agnihotri) wrote:

> Have a file containing key columns + additive facts. A table in the
> database needs to be updated using this file. If the row exists based on
> the key, update the row otherwise insert it.
>
> Which of the following methods do you think will be the fastest and why?
>
> Method 1:
> --------
> snip
> Method 2:
> --------
> snip
> Method 3:
> --------
> snip

In most circumstances, the most direct method is also the most efficient:

1. Update

   ---

   update main_table MT set (col1,col2,col3,..) = (select UT.col1 + MT.col1 ,UT.col2 + MT.col2 ,UT.col3 + MT.col3 ,... from update_table UT where UT.key = MT.primary_key) -- make sure UT.key is indexed where MT.primary_key in (select key from update_table);
2. Insert

   ---

   insert into main_table (column_list) select col1,col2,col3,... from update_table UT where not exists (select null from main_table MT where MT.primary_key = UT.key)

HTH

-- 
Chrysalis

"FABRICATE DIEM PVNC"
(To to protect and to serve)
Motto of the City Guard
Terry Pratchett