Re: Using multiple tables in update

Sean Garagan (garagan_at_ug.cs.dal.ca) wrote:
: Greetings all,
 

: I am trying to update one table using information from a second table, but
: the information is very specific. The query I am using is similar to:
 

: UPDATE table1
: SET table1.field1 = table2.field1
: WHERE table1.fieldA = table2.fieldA
: etc...
 

: This gives me an unknown column error for the table2 columns, even when I
: use the full user.table.column nomenclature. I have also tried to use
: an embedded SELECT, but then I have no way of specifying which columns get
: updated.
 

: I am running Oracle 7.0.13 on an SVR4 Unix box.
 

: Any help would be greatly appreciated,
: Sean
: garagan_at_ug.cs.dal.ca

Update table1 t1

       set t1.field1 = (select t2.field2
			from table2 t2
			where t2.fieldA = t1.fieldA
			)
	where exists ( select 'x'
			from table2 t3
			where t3.fieldA = t1.fieldA
			);

hope it helps...
Srini Kunamneni Received on Wed Jul 24 1996 - 00:00:00 CEST