Re: Weird ORACLE update statement

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 Received on Tue Dec 08 1992 - 22:04:28 CET