Re: Bulk updates in SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 26 Jan 2007 15:14:52 -0800
Message-ID: <1169853288.588527@bubbleator.drizzle.com>

dean wrote:

> 
> On Jan 26, 3:15 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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.

> 
> Thanks! That's very interesting, I'll look into that some time. But
> now, do you know of an UPDATE version also, which is what I was
> originally posting about?
> 
> Dean

No.

You can find more demos, like the ones I posted above, in Morgan's Library at www.psoug.org.

-- 
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

Received on Fri Jan 26 2007 - 17:14:52 CST