Re: Weird ORACLE update statement
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