merge and ORA-30926

From: Ls Cheng <exriscer_at_gmail.com>
Date: Fri, 26 Sep 2014 00:12:50 +0200
Message-ID: <CAJ2-Qb_5bM5gwAtx=68TyY=efM_S4kV2sxnm3iiDF45RG99sjg_at_mail.gmail.com>

Hi

I am recoding some update statements which uses BYPASS_UJVC hint into merge statements.

While doing this and testing some codes I noticed something which I dont understand, the following gives ORA-30926

create table t11 ( x int, y int );

create table t12 ( x int, y int );

insert into t11 values ( 1, null );

insert into t12 values ( 1, 1 );

insert into t12 values ( 1, 2 );

merge into t11 using t12 on (t11.x = t12.x) when matched then update set y = t12.y;

merge into t11 using t12 on (t11.x = t12.x)

                     *

ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

But if instead of inserting null to t11, I insert 1 as following

create table t11 ( x int, y int );

create table t12 ( x int, y int );

insert into t11 values ( 1, 1);

insert into t12 values ( 1, 1 );

insert into t12 values ( 1, 2 );

merge into t11 using t12 on (t11.x = t12.x) when matched then update set y = t12.y;

2 rows merged.

merge goes through and updates t11.y twice to end up with value 2

select * from t11,t12 where t11.x = t12.x;

         X Y X Y
---------- ---------- ---------- ----------

         1          2          1          1
         1          2          1          2

I was expecting it fails as well since AFIAK merge should not update same row twice, correct?

Any ideas?

Thanks

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 26 2014 - 00:12:50 CEST