Re: Help - where is FROM clause in update?

danj_at_cbnewsg.cb.att.com (Dan Jackson) writes:

>How do you include a join operation when using SQL to
>perform an update? I am using Oracle 6.0 with embedded
>SQL. I have some values from my embedded program and
>I want to populate a table with these values AND I
>also need some values from other tables.
 

>the SQL syntax seems to say that you can "set column = value"
>or "set column = (query)" I need to do both. If I have 5
>values that I am updating from another table, do I have
>to "set column = (query)" 5 times, once from each value?
 

>I am use to using Ingres 6.4 which DOES have a 'from' clause
>which makes this very easy. Does Oracle 7 have it?
>Is there an ANSI standard?

How about (using Pro*C as example):

    EXEC SQL

	update	table1 t1
	set	(col1, col2, col3, col4	) = (
		select	t2.col5, t2.col6, :b_var1, :b_var2
		from	table2 t2
		where	<your favorite joint conditions>
	)
	where exists (
		select	'x'
		from	table2
		where	<again your favorite joint conditions>
	);

Hope this helps.

--
 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