Re: Bulk updates in SQL

dean schrieb:
> 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.
>

Search this group may help in many cases. http://groups.google.de/group/comp.databases.oracle.server/browse_frm/thread/2748be7840864b6/28b7b4318c62276b?lnk=gst&q=Jaap+update+decode&rnum=2&hl=de#28b7b4318c62276b http://groups.google.de/group/comp.databases.oracle.server/browse_frm/thread/d4d289eeb1d81c34/bad2b51362cf04b3?lnk=gst&q=+update+decode&rnum=1&hl=de#bad2b51362cf04b3 for example discuss similar question

Best regards

Maxim Received on Fri Jan 26 2007 - 13:32:18 CST