Re: help: many updates

On Sun, 23 Feb 2003 14:41:40 +1100, "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:

>On Sat, 22 Feb 2003 03:57:47 +0000, charlie cs wrote:
>
>> We are using 9.2.
>> We need to update a big table like this
>>
>> SQL> update table test set colA='A' where colA_1='X';
>> SQL> update table test set colB='B' where colB_1='Y';
>> SQL> update table test set colC='C' where colC_1='Z';
>>
>> .....
>>
>> Is there any way we can do this in one sql, instead of making several
>> database calls?
>>
>> Thanks for you help.
>
>
>The closest I can think of is the new 9i MERGE statement, which says 'if
>this record is found in table 1, update it so it matches the values in
>table 2. Otherwise, insert it into table 1 as a brand new record'.

I would use the PL/SQL FORALL statement. Put 'A','B','C' in one collection, 'X','Y','Z' in another collection, and run something like

FORALL i IN collection1.first .. collection1.last

        UPDATE mytable SET col1 = collection1(i) WHERE col2 = collection2(i);

John Received on Fri Feb 28 2003 - 01:41:09 CST