Re: Weird ORACLE update statement

From: Stephen Strobel <strobel_at_newton.mcs.gvsu.edu>
Date: 12 Dec 92 14:32:57 GMT
Message-ID: <1992Dec12.143257.8808_at_mcs.gvsu.edu>


In article <1992Dec8.210428.22025_at_cbnewsk.cb.att.com> jrmo_at_prpix2.att.com (J.R.Moore) writes:
>In article <1992Dec8.153620.9115_at_infonode.ingr.com> mcintyre_at_infonode.ingr.com (John Scott Mcintyre) writes:
>> I am using ORACLE and want to be able to update rows in table1 using
>> columns from table2 when the two tables can be joined.
>>
>> The SQL statement I would like to perform would look like this:
>>
>> update table1 set table1.manufacturer=table2.manufact,
>> set table1.pur_cost=table2.sale_cost
>> where table1.inv_number=table2.inv_number;
>>
>> The problem is UPDATE apparently cannot handle multiple tables.
>>
>Try this:
>
>UPDATE table1 A
>SET manufacturer=(
> SELECT table2.manufact
> FROM table2
> WHERE A.inv_number=table2.inv_number
> ),
> pur_cost=(
> SELECT table2.sale_cost
> FROM table2
> WHERE A.inv_number=table2.inv_number
> )
>WHERE inv_number IN (
> SELECT table2.inv_number
> FROM table1,table2
> WHERE table1.inv_number=table2.inv_number
> )
>;
>
>Joe Moore

Better yet:

Update Table1 A
Set (manufacturer, pur_cost) =

    (Select manufacturer, sale_cost

     from   table2 B
     where  A.inv_number = B.inv_number)
-- 
|--------------------------------------|--------------------------------------|
|  Stephen Strobel                     | Phone: (h) (616) 235-1263            |
|  Grand Valley State University       |        (w) (616) 940-4241            | |                                      |  Mail: strobel_at_newton.mcs.gvsu.edu   |
|--------------------------------------|--------------------------------------|
Received on Sat Dec 12 1992 - 15:32:57 CET

Original text of this message