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.
>
> I have also tried different variations of views but cannot
> get the view to contain the joined and unjoined rows.
>
> If anyone has any ideas I would really appreciate the help.

Try this:

	UPDATE TABLE1
	SET (MANUFACTURER, PUR_COST) = (
		SELECT	MANUFACT, SALE_COST
		FROM	TABLE2
		WHERE	TABLE1.INV_NUMBER = TABLE2.INV_NUMBER
	);

-- 
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 Alvin W. Law                 Manufacturing Applications, Oracle Corporation
 alaw_at_us.oracle.com                 Voice: 415.506.3390    Fax: 415.506.7299
    This message is brought to you by 100% recycled electrons. --,--'-<{_at_