Brian Albert wrote:
I'm not sure why this worked - but it did.
Brian,
If you compare your query to the one Samuel suggested you will see a
subtle difference. Your query uses a join in the from clause and his
does not.
update table1
set column3 = (
select i.colum3
from table2 i, table1 t
where t.column1 = i.column1
and t.column2 = i.column2)
UPDATE table1
SET column3 = (
SELECT column3
FROM table2
WHERE table2.column1 = table1.column1
AND table2.column2 = table1.column2)
Your sub query can be completely evaluated regardless of the currently
active row in the outer update scan, and so it is. Your query does not
depend upon the values in table1 row being updated. The entire sub query
is evaluated once, and the value of column3 from the first result row is
returned as the value of the sub query expression, and then used to
update every row in table1.
In his query, the row from table2 to be selected in the sub query
depends upon the value of table1.column1 and table1.column2 in the row
being updated. This turns the sub query into a correlated sub query
which must be re-evaluated for each row in table 1. Each evaluation
returns the value of column3 from a different row in table2, the row
where the column2 and column2 values match the row being updated.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
