ORA-30926 - Merge state

Question

I am getting an ORA-30926 error. I researched this and found that this is usually caused by duplicates in the query specified in theUSING clause.

The problem is I am intially removing duplicates and storing in a temperory table (temp_distinct) which in turn I am referring to in the MERGE. Here is my code snippet:

MERGE INTO name_test nt 
USING (select name from temp_distinct) s 
ON (1=1)
WHEN MATCHED
THEN UPDATE SET nt.fn = s.name, nt.LN = s.name

Here is the structure of my tables:

NAME_TEST:

FN    LN
----- -----
Ruc   Rag
Ruc   Ran
Sam   Kum
Ruc   Ran
Ruc   Kum
Ran   Dev
Rag   Agar
Rag   Ran

TEMP_DISTINCT:

FN    NUMB NAME  NUM
----- ---- ----- ---
Sam   1    Mark  1
Rag   2    Steve 2
Dev   3    John  3
Kum   4    Dave  4
Ruc   5    Mich  5
Agar  6    Dean  6
Ran   7    Phil  7

So as you can see there is no duplicate in USING clause. I am trying to replace NT.FN = S.NAME and also NT.LN = S.NAME.

Basically I want to replace names in FN and LN from NAME_TEST table with different name from TEMP_DISTINCT table. Final output should be like below:

FN     LN
------ ------
Mich   Steve
Mich   Phil
Mark   Dave
Mich   Phil
Mich   Dave
Phil   John
Steve  Dean
Steve  Ran

Answer 1

Your query can't be executed because of (1 = 1) oracle can't get a stable set of rows, to be clear check this out:

SQL> create table ttt (name varchar2(20 char));

Table created.

SQL> insert into ttt values('first1');

1 row created.

SQL> insert into ttt values('second2');

1 row created.

SQL> merge into ttt t1 using (select 'name' name from dual) t2 on (1 = 1) when matched then update set t1.name = t2.name;

2 rows merged.

SQL> select * from ttt;

NAME
--------------------
name
name

There is only one row in the "t2" subquery and the merge operations has been successfully finished. But if your subquery gets you more than one line I will encounter the ORA-30926:

SQL> merge into ttt t1 using (select 'name' || level name from dual connect by rownum < 4) t2 on (1 = 1) when matched then update set t1.name = t2.name;
merge into ttt t1 using (select 'name' || level name from dual connect by rownum < 4) t2 on (1 = 1) when matched then update set t1.name = t2.name
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


SQL> select * from ttt;

NAME
--------------------
name
name

SQL>

Oracle doesn't know what value has to be associated with the rows. Your result set is ambiguous. To be able to perform this merge you have to have a normal condition for example:

SQL> alter table ttt add id number(10);

Table altered.

SQL> update ttt set name = 'name1', id = rownum;

2 rows updated.

SQL> select * from ttt;

NAME                         ID
-------------------- ----------
name1                         1
name1                         2

SQL> merge into ttt t1 using (select 'name' name, level id from dual connect by rownum < 4) t2 on (t1.id = t2.id) when matched then update set t1.name = t2.name;

2 rows merged.

SQL> select * from ttt;

NAME                         ID
-------------------- ----------
name                          1
name                          2

SQL>

As you can see the "ttt" table has been merged successfully.

Answer 2

In addition to the explanation from @zaratustra, your merge is also attempting to set the fn and ln to the same name value so it wouldn't give the result you want even it worked. And you can't use fn or ln in the using clause as you're trying to update them.

If your name_test table had a primary key (or at least unique) column then you could include that in the merge, but you'd still only be able to correctly update either the fn or ln value in one pass.

I'm not sure why you're not doing a simple update:

update name_test nt
set fn = (select td.name from temp_distinct td where td.fn = nt.fn),
  ln = (select td.name from temp_distinct td where td.fn = nt.ln);

8 rows updated.

select * from name_test;

FN    LN  
----- -----
Mich  Steve 
Mich  Phil  
Mark  Dave  
Mich  Phil  
Mich  Dave  
Phil  John  
Steve Dean  
Steve Phil