Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating Multiple Columns in Multiple Rows

Re: Updating Multiple Columns in Multiple Rows

From: Brian Tkatch <N/A>
Date: Fri, 12 Oct 2007 10:26:12 -0400
Message-ID: <uq0vg3pd50ok76ogoajljm49qdrg7ngij1@4ax.com>


On Fri, 12 Oct 2007 10:07:05 -0400, Brian Tkatch <N/A> wrote:

>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.

Of course, if it's just speed we're after, we might as well UPDATE every row:

UPDATE
        Test
SET

	Id =
	NVL(
		(
		 SELECT
			T_New.Id
		 FROM
			T_Old,
			T_New
		 WHERE
			T_Old.Id	= Test.Id
		   AND	T_Old.Acc	= T_New.Acc
		),
	Test.Id);

At least in this small case it seems to be faster.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US