Re: Bulk updates in SQL

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

Bad tip. Why not use the technology already in Oracle specifically for that purpose?

INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id) SELECT program_id, delivered_date, customer_id, order_date FROM airplanes;

or

INSERT ALL
WHEN (deptno=10) THEN

   INTO emp_10 (empno,ename,job,mgr,sal,deptno)    VALUES (empno,ename,job,mgr,sal,deptno) WHEN (deptno=20) THEN

   INTO emp_20 (empno,ename,job,mgr,sal,deptno)    VALUES (empno,ename,job,mgr,sal,deptno) WHEN (deptno<=30) THEN

   INTO emp_30 (empno,ename,job,mgr,sal,deptno)    VALUES (empno,ename,job,mgr,sal,deptno) ELSE
   INTO leftover (empno,ename,job,mgr,sal,deptno)    VALUES (empno,ename,job,mgr,sal,deptno) SELECT * FROM emp;

It is far more flexible.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org