Re: Using multiple tables in update

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

Sean:

        you must explicit reference the other table (table2). The update clause implicitly references table1.

UPDATE table1

    SET table1.field1 = (select table2.field1

    			    FROM table2
			    WHERE table1.fieldA = table2.fieldA
			         etc...)
	where table1.fieldA  ...meets some condition...

The first table (table1) can be referenced in the embedded selection. The challenge is that the embedded select return one and only one row each time.

Addition of distinct or group by or max or min values may be needed to ensure that there is only one value to use.

In addition, you probably do not what to update rows where there is no match. Hence the final where clause would have to written to select only those rows in the table1. Typical ideas is table1.fieldA

	table1.fieldA exists in (select ... )
	OR table1.fieldA = x

Mike Krolewski mkrolews_at_upw.com Received on Tue Jul 23 1996 - 00:00:00 CEST