Re: Update with join

and123456_at_my-dejanews.com wrote:

> 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

  If table1 is a key-reserved table in the join, you can do the following:

  create view v1 as
  select t1.col col_1, t2.col col_2
  from table1 t1, table2 t2
  where t1.id = t2.id;

  update v1
  set col_1 = col_2;

  Hope it helps. Received on Thu Jan 14 1999 - 04:56:14 CST