Re: Updating Multiple Columns in Multiple Rows

On Thu, 11 Oct 2007 07:05:58 -0700, "fitzjarrell_at_cox.net" <fitzjarrell_at_cox.net> wrote:

>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

Interesting.

Both queries use a WHERE clause, but the first only searches T_Old with looking for a match in T_New. Thus, if T_Old has it but T_New does not, it would be replaced with a NULL. The second query would ignore that case entirely.

If the join is removed from the EXISTS clause of the second query, the cost goes down dramatically, and is comparable to the first query.

B. Received on Fri Oct 12 2007 - 09:07:05 CDT