Merge query returning ORA-30926: unable to get a stable set of rows in the source tables

Question

I have two merge queries which triggers one after another

First Query

merge into MyTable
using
(   
select distinct nullLogSetId.Id as IdToUpdate,
       knownLogSetId.LogSetId LogSetIdToUpdateTo
from MyTable knownLogSetId 
join MyTable nullLogSetId
  on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType
 and knownLogSetId.Identifier = nullLogSetId.Identifier 
where 

    knownLogSetId.IdentifierType = 'ABC'
    and knownLogSetId.LogSetId >= 0 
    and nullLogSetId.LogSetId = -1
)
on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo

Second Query

merge into MyTable
using
(

  select distinct nullLogSetId.Id as IdToUpdate,
                  knownLogSetId.LogSetId LogSetIdToUpdateTo
  from MyTable knownLogSetId 
  join MyTable nullLogSetId
    on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType 
   and knownLogSetId.Identifier = nullLogSetId.Identifier 
  where 
    knownLogSetId.IdentifierType = 'DEF'
    and knownLogSetId.LogSetId >= 0 
    and nullLogSetId.LogSetId = -1
) on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo

I am calling these queries from .NET one after another using OracleCommand

The first works fine but when second gets triggered i am getting error

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

I have read all the relevent questions and tried from my side as well , if you see i used distinct in both of the queries so duplicating of rows is not an issue. Can please anybody help me out what i am i doing wrong , it could be a basic stuff as i am new to queries , please help me out

Solution

if you see i used distinct in both of the queries so duplicating of rows is not an issue.

You probably have duplicates in data. DISTINCT does not guarantee you have IdToUpdate unique when you use it with other columns. See:

CREATE TABLE #MyTable(IdToUpdate INT, LogSetIdToUpdateTo INT);

INSERT INTO #MyTable VALUES (1,1), (1,2), (2,1),(3,1);

SELECT DISTINCT IdToUpdate, LogSetIdToUpdateTo
FROM #MyTable;

LiveDemo

You will get IdToUpdate twice. Check your data:

with cte AS (
  select distinct nullLogSetId.Id as IdToUpdate,
                  knownLogSetId.LogSetId LogSetIdToUpdateTo
  from MyTable knownLogSetId 
  join MyTable nullLogSetId
    on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType 
   and knownLogSetId.Identifier = nullLogSetId.Identifier 
  where 
    knownLogSetId.IdentifierType = 'DEF'
    and knownLogSetId.LogSetId >= 0 
    and nullLogSetId.LogSetId = -1
)
SELECT IdToUpdate, COUNT(*) AS c
FROM cte
GROUP BY IdToUpdate
HAVING COUNT(*) > 1;

One way to go is to use aggregation function(MAX/MIN) instead of DISTINCT:

merge into MyTable
using
(

  select nullLogSetId.Id as IdToUpdate,
         MAX(knownLogSetId.LogSetId) AS LogSetIdToUpdateTo 
  from MyTable knownLogSetId 
  join MyTable nullLogSetId
    on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType 
   and knownLogSetId.Identifier = nullLogSetId.Identifier 
  where 
    knownLogSetId.IdentifierType = 'DEF'
    and knownLogSetId.LogSetId >= 0 
    and nullLogSetId.LogSetId = -1
  GROUP BY nullLogSetId.Id
) on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo

Comments

[Anamay]

Thanks alot @lad2025 the distinct cause me overconfident your answer was perfect.

[Lukasz Szozda]

@Anamay You're welcome. One lesson in SQL world is to never take anything for granted. Check it first. :)