Re: Updating Multiple Columns in Multiple Rows

From: <fitzjarrell_at_cox.net>
Date: Thu, 11 Oct 2007 07:05:58 -0700
Message-ID: <1192111558.368600.70830@v3g2000hsg.googlegroups.com>

On Oct 11, 8:52 am, Brian Tkatch <N/A> wrote:
> On Wed, 10 Oct 2007 16:00:03 -0700, kc <kecann..._at_hotmail.com> wrote:
> >-- The error on this attempt is:
> >-- ORA-01407: cannot update ("PROSURV_0701"."TEST"."ID") to NULL
> >-- Even though I'm specifying records in 1 of the correlating tables,
> >it wants
> >-- to hit every row and set non-matches to null
> >UPDATE TEST T1
> >SET ( ID ) = (
> > SELECT T_OLD.ID
> > FROM TEST T2
> > JOIN T_OLD ON T2.ID = T_OLD.ID
> > JOIN T_NEW ON T_OLD.ACC = T_NEW.ACC
> > LEFT JOIN T_OLD T_OLD_2 ON T_OLD_2.ID = T2.ID
> > WHERE
> > T2.ID <> T_OLD.ID
> > AND T_OLD_2.ID IS NULL
> > AND T1.ID = T2.ID
> >)
> >WHERE T1.ID IN (
> > SELECT ID FROM T_OLD
> >);

> This is a problem because the EXISTS does not refer to the join. It
> could be fixed by making the join (and only UPDATEing changed values)
> or adding NVL() to the subquery, and supplying the old value as the
> defult.

> This should work:

>


> UPDATE

>         Test

> SET

>         Id =

>         (

>          SELECT

>                 T_New.Id

>          FROM

>                 T_Old,

>                 T_New

>          WHERE

>                 T_Old.Id        = Test.Id

>            AND  T_Old.Acc       = T_New.Acc

>         )

> WHERE

>         EXISTS

>         (

>          SELECT

>                 *

>          FROM

>                 T_Old,

>                 T_New

>          WHERE

>                 T_Old.Id        = Test.Id

>            AND  T_Old.Acc       = T_New.Acc

>         )

>

> B.- Hide quoted text -

>
> - Show quoted text -

It does, thus the OP has a choice:

SQL>
SQL> -- This works
SQL>
SQL>
SQL> update test t1
  2  set id = (select id from t_new
  3            where t_new.acc = (select acc from t_old where id =

t1.id))
4 where t1.id in (select id from t_old);

2 rows updated.

Execution Plan

Plan hash value: 4072432620

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)|
Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |       |     3 |    78 |     7  (15)|
00:00:01 |
|   1 |  UPDATE             | TEST  |       |       |
|          |
|*  2 |   HASH JOIN SEMI    |       |     3 |    78 |     7  (15)|
00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST  |     5 |    65 |     3   (0)|
00:00:01 |
|   4 |    TABLE ACCESS FULL| T_OLD |     2 |    26 |     3   (0)|
00:00:01 |
|*  5 |   FILTER            |       |       |       |
|          |
|   6 |    TABLE ACCESS FULL| T_NEW |     2 |    62 |     3   (0)|
00:00:01 |
|*  7 |    TABLE ACCESS FULL| T_OLD |     1 |    31 |     3   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   2 - access("T1"."ID"="ID")
   5 - filter("T_NEW"."ACC"= (SELECT "ACC" FROM "T_OLD" "T_OLD" WHERE
              "ID"=:B1))
   7 - filter("ID"=:B1)

Note

dynamic sampling used for this statement

Statistics

         14  recursive calls
          2  db block gets
         74  consistent gets
          0  physical reads
        496  redo size
        386  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          2  rows processed

Notice that this statement generated 14 recursive calls and 3 sorts.

SQL>
SQL> -- We'll try this
SQL>
SQL> UPDATE
  2          Test
  3  SET
  4          Id =
  5          (
  6           SELECT
  7                  T_New.Id
  8           FROM
  9                  T_Old,
 10                  T_New
 11           WHERE
 12                  T_Old.Id        = Test.Id
 13             AND  T_Old.Acc       = T_New.Acc
 14          )
 15  WHERE
 16          EXISTS
 17          (
 18           SELECT
 19                  *
 20           FROM
 21                  T_Old,
 22                  T_New
 23           WHERE
 24                  T_Old.Id        = Test.Id
 25             AND  T_Old.Acc       = T_New.Acc
 26          );

2 rows updated.

Execution Plan

Plan hash value: 496111780

| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)|
Time     |
---------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |         |     5 |   130 |    10  (10)|
00:00:01 |
|   1 |  UPDATE               | TEST    |       |       |
|          |
|*  2 |   HASH JOIN SEMI      |         |     5 |   130 |    10  (10)|
00:00:01 |
|   3 |    TABLE ACCESS FULL  | TEST    |     5 |    65 |     3   (0)|
00:00:01 |
|   4 |    VIEW               | VW_SQ_1 |     2 |    26 |     7  (15)|
00:00:01 |
|*  5 |     HASH JOIN         |         |     2 |    98 |     7  (15)|
00:00:01 |
|   6 |      TABLE ACCESS FULL| T_OLD   |     2 |    62 |     3   (0)|
00:00:01 |
|   7 |      TABLE ACCESS FULL| T_NEW   |     2 |    36 |     3   (0)|
00:00:01 |
|   8 |   NESTED LOOPS        |         |     1 |    62 |     6   (0)|
00:00:01 |
|*  9 |    TABLE ACCESS FULL  | T_OLD   |     1 |    31 |     3   (0)|
00:00:01 |
|* 10 |    TABLE ACCESS FULL  | T_NEW   |     1 |    31 |     3   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   2 - access("ID"="TEST"."ID")
   5 - access("T_OLD"."ACC"="T_NEW"."ACC")
   9 - filter("T_OLD"."ID"=:B1)
  10 - filter("T_OLD"."ACC"="T_NEW"."ACC")

Note

dynamic sampling used for this statement

Statistics

         47  recursive calls
          4  db block gets
        169  consistent gets
          0  physical reads
          0  redo size
        390  bytes sent via SQL*Net to client
        786  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> This statement generated 47 recursive calls and 15 sorts. It might be more efficient to use the first statement rather than the second.

David Fitzjarrell Received on Thu Oct 11 2007 - 09:05:58 CDT