CODEWITHSUNDEEP
sqloracle-databasemerge
Symonds
Symonds

Reputation: 194

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

I really don't understand why I am getting error with the below query as ORA-30926: unable to get a stable set of rows in the source tables as i have provided distinct clause in my merge query and also BB_TST_HISTORY has appropriate unique constraint.

merge into bb_tst_history h
using (select distinct r.ausuebungsbezeichnung
       from   bb_tst_rollup r
       join   ( select root, boerse, security_typ
                from   bb_export_filter
                minus
                select root, boerse, security_typ
                from   bb_tst_exception
              ) e
              on   r.root = e.root
              and  r.security_typ = e.security_typ
              and  r.boerse = e.boerse
      ) s
on (s.root = h.root and s.security_typ = h.security_typ and s.boerse = h.boerse)
when matched then
    update set h.ausuebungsbezeichnung = s.ausuebungsbezeichnung
when not matched then
    insert values (s.ausuebungsbezeichnung);

Upvotes: 0

Views: 1184

Answers (2)

Shantanu Kher
Shantanu Kher

Reputation: 1054

This error occurs because you are getting more than one row in your source for the joining columns. Best way to approach this issue is, identifying those records which are causing issue. Below SQL can give you those records -

select root, security_typ, boerse, count(1) from
(select distinct r.AUSUEBUNGSBEZEICHNUNG from BB_TST_ROLLUP r,      
          (select ROOT, BOERSE, SECURITY_TYP from BB_EXPORT_FILTER minus 
select ROOT, BOERSE, SECURITY_TYP from BB_TST_EXCEPTION)) 
group by root, security_typ, boerse having count(1) > 1;

You need to update additional join condition in 'ON' clause to fix this issue.

Updated Merge Statement -

merge into BB_TST_HISTORY h   using  
    (    select ROOT, BOERSE, SECURITY_TYP, AUSUEBUNGSBEZEICHNUNG from (select distinct r.AUSUEBUNGSBEZEICHNUNG from BB_TST_ROLLUP r       
        join     
          (select ROOT, BOERSE, SECURITY_TYP from BB_EXPORT_FILTER minus 
select ROOT, BOERSE, SECURITY_TYP from BB_TST_EXCEPTION))) e       
        on      r.root=e.root and r.security_typ=e.security_typ and r.boerse=e.boerse  ) s 
on (s.root=h.root and s.security_typ=h.security_typ and s.boerse=h.boerse) 
        when matched then   update set h.AUSUEBUNGSBEZEICHNUNG = s.AUSUEBUNGSBEZEICHNUNG
        when not matched then   insert values (s.AUSUEBUNGSBEZEICHNUNG);

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You must have single record for matching criteria in the USING clause.

so you can use the GROUP BY and MAX in USING clause as follows:

MERGE INTO BB_TST_HISTORY H
USING (
          SELECT MAX(R.AUSUEBUNGSBEZEICHNUNG) AS AUSUEBUNGSBEZEICHNUNG, 
                 R.ROOT, R.SECURITY_TYP, R.BOERSE
            FROM BB_TST_ROLLUP R
            JOIN (
              SELECT ROOT, BOERSE, SECURITY_TYP
                FROM BB_EXPORT_FILTER
              MINUS
              SELECT ROOT, BOERSE, SECURITY_TYP
                FROM BB_TST_EXCEPTION
          ) E
          ON R.ROOT = E.ROOT
             AND R.SECURITY_TYP = E.SECURITY_TYP
             AND R.BOERSE = E.BOERSE
         GROUP BY R.ROOT, R.SECURITY_TYP, R.BOERSE
      ) S ON ( S.ROOT = H.ROOT
   AND S.SECURITY_TYP = H.SECURITY_TYP AND S.BOERSE = H.BOERSE )
WHEN MATCHED THEN UPDATE
   SET H.AUSUEBUNGSBEZEICHNUNG = S.AUSUEBUNGSBEZEICHNUNG
WHEN NOT MATCHED THEN
INSERT VALUES ( S.AUSUEBUNGSBEZEICHNUNG );

Upvotes: 1

Related Questions