Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Updating a table
I thougth this one is a better option to consider too ..
Chrysalis wrote:
> 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
Received on Wed Apr 29 1998 - 00:00:00 CDT
![]() |
![]() |