Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Updating a table

Re: Updating a table

From: Jojo <jojo_at_nowhere.com>
Date: 1998/04/29
Message-ID: <35479276.55F50D2B@nowhere.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US