Bulk updates in SQL

Hello all,

I got some great tips here some years ago on using union all to create sql statements that insert multiple rows into a table in one statement, something like

create table T (F1 number, F2 number);

insert into T(
select 1,2 from dual
union all
select 3,4 from dual
union all
select 5,6 from dual);

3 rows created.

Now, I would really like something for updating a table, where individual values are known for individual keys.

In pseudo-SQL this would be:

update T
set F2=10 where F1=1,

     F2=20 where F1=2,
     F2=25 where F1=3;

But of course that does not work. Anyone have a simple solution here?

So far I can think of 2 methods, neither of which I think are particularly elegent. One is to make a subquery based on the keys and the values, and perform a correlated (joined) update:

In this case I make a subquery that creates a dataset of values
(F1=1,F2=100),(F1=3,F2=300) and do a correlated update joining "where
T.F1 = V.F1". I also have to add the second where statement so that I don't NULL out any records where there is no match.

update T
set F2 =
(

  select F2 from
  (
    select 1 as F1, 100 as F2 from dual
    union all
    select 3 as F1, 300 as F2 from dual
  ) V
  where T.F1 = V.F1
)
where F1 in
(

  1,3
)

The other method I can think of is to create a temp table and do a fast insert into that, and then do a single-call update using a correlated query.

Any other ideas would be welcomed.

Thanks

Dean

Oracle 9.2, 10.1, Windows. Received on Fri Jan 26 2007 - 13:17:06 CST