Re: Updating correlated subqueries

On Jan 26, 1:57 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
> create table T (F1 number, F2 number);
> create table U (F1 number, F2 number);
>
> insert into T values (1,1);
> insert into T values (2,2);
> insert into T values (3,3);
> insert into T values (4,4);
>
> insert into U values (1,111);
> insert into U values (2,222);
>
> commit;
>
> select * from T;
>
> F1 F2
> ---------- ----------
> 1 1
> 2 2
> 3 3
> 4 4
>
> 4 rows selected.
>
> --UPDATE QUERY
> update T
> set F2 =
> (
> select F2
> from U
> where U.F1 = T.F1
> )
> where T.F1 in (select F1 from U);
>
> 2 rows updated.
>
> select * from T;
>
> F1 F2
> ---------- ----------
> 1 111
> 2 222
> 3 3
> 4 4
>
> 4 rows selected.
>
> Ok, given the above simplified update on a correlated query, is there
> any other way that might be more efficient that using the IN clause for
> the WHERE statement? Basically I only want to update the values where
> there is a match.
>
> Thanks!
>
> Dean
> Oracle 9.2, 10.1, Windows.

Something like this will likely work:
UPDATE
  T
SET
  F2=NVL(
    (SELECT DISTINCT
      F2
    FROM
      U
    WHERE
      U.F1=T.F1),
    F2);

4 ROWS UPDATED SELECT
  *
FROM
  T;

F1 F2

 1	  111
 2	  222
 3	    3
 4	    4

Note the side effects: inaccurate count of the number of rows updated, as well as increased redo and undo. In the above SQL statement, if the value returned by the subquery is NULL, the original value of T.F2 is written over the top of the existing, original value of T.F2. The original solution that you posted is a better solution than the shortcut solution that I posted due to the side effects.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jan 26 2007 - 20:39:59 CST