Update with join

In SQL Server you can update with a join like: Update table1
From table1 t1, table2 t2
Set t1.col = t2.col
Where t1.id = t2.id;

Oracle does not support Update with join. The closest thing is Update with subquery:
Update table1 t1
Set col = (Select col from table2 where id=t1.id);

However, if there is no row in table2, col in table1 will be set to NULL. Using Update table1 t1 Set col = (Select col from table2 where id=t1.id) Where exists (Select 1 from table2 where id=t1.id); will access table2 (or index) twice which is not quite efficient.

Anyone has better ideas? I believe using cursor in PL/SQL will make it even slower because you individually select and update each record, right?

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 13 1999 - 12:44:12 CST