Re: Sqlplus: Embedded update statement

pkane_at_cisco.com (Peter Kane) writes:
>Hi Oracle gurus, please help if you can.
 

>I have two tables, call them "order" and "header",
>and they both have two columns called "value_id" and "lead".
>
>I want something like:
>
> update header.lead, setting header.lead = order.lead
> where header.value_id = order.value_id
>
>In other words, I would like to copy lead from one table to another,
>where the value_id is the same. I'd like to do it in an embedded
>SQL statement if I can, but I guess I'm afraid I'll have to use
>cursor, or PL/SQL or something.
 

>Is there an easy way to do something like this?

Yes. Yes. Yes. There's no need to use cursor or PL/SQL. Try:

	UPDATE HEADER H
	SET LEAD = (
		SELECT	LEAD
		FROM	ORDER O
		WHERE	O.VALUE_ID = H.VALUE_ID
	);

--
 Alvin W. Law ........................................... Oracle Corporation
 Senior Applications Engineer ............... 300 Oracle Parkway, Box 659306
 Oracle Manufacturing ............................. Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ....... Voice: 415.506.3390 ...... Fax: 415.506.7299