Re: Update with join

and123456_at_my-dejanews.com ¼¶¼g©ó¤å³¹ <77iphp$5mh$1_at_nnrp1.dejanews.com>...
>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?

If you use Oracle 7.3 or later, you can use this:

 update (select t1.col c1, t2.col c2 from t1, t2 where t1.id=t2.id)    set c1=c2; Received on Wed Jan 20 1999 - 09:06:35 CST