[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1003
  • Last Modified:

ORA-30926 on Merge script

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

Hello everyone I am back. i am getting a ORA-30926 on the below merge script. I had this working but for some reason I am getting this now. I have looked all over the web and I can not figure out why. Can some please give me some direction on what might be going on?


Any help would be great.
MERGE INTO TEST.CONTCT D
USING         (select WHOLENAME, FIRST_NAME, LAST_NAME, ADUSERNAME, EMAIL, PHONENUMBER, LOCATION1, DEPARTMENT, COMPANY, STATUS, LOCATION_L7
               from (
                      SELECT P7.WHOLENAME, P7.FIRST_NAME, P7.LAST_NAME, P7.ADUSERNAME, P7.EMAIL, P7.PHONENUMBER, 
                             P7.LOCATION1, P7.DEPARTMENT, P7.COMPANY, P7.STATUS,L7."LOCATION" AS LOCATION_L7,
                             row_number() over(partition by P7.ADUSERNAME order by P7.ADUSERNAME) AS RN
                       FROM LNOTES_EXP P7, TEST.LOCM1 L7
                       WHERE L7.LOCATION_NAME = P7.LOCATION1
                       AND P7.ADUSERNAME is not null
                     )
                where RN = 1
              ) P
ON  
           (UPPER(P.ADUSERNAME) = D.CONTACT_NAME)
WHEN MATCHED THEN
              UPDATE
              SET  
                              D.FULL_NAME = P.WHOLENAME,                            
                              D.FIRST_NAME = P.FIRST_NAME,
                              D.LAST_NAME = P.LAST_NAME,
                              D.EMAIL = P.EMAIL,
                              D.CONTACT_PHONE = P.PHONENUMBER,
                              D."LOCATION" = P.LOCATION_L7 ,                                  
                              D.DEPT_NAME = P.DEPARTMENT ,
                              D.COMPANY  = 'EMG',
                              D.ACTIVE  = DECODE(UPPER(P.STATUS),'ACTIVE','Y','N')
WHEN NOT MATCHED THEN 
              INSERT
              (D.FULL_NAME, D.CONTACT_NAME, D.USER_ID, D.FIRST_NAME, D.LAST_NAME, D.OPERATOR_ID,
               D.EMAIL, D.CONTACT_PHONE  ,D."LOCATION", D.DEPT_NAME, D.COMPANY,
               D.ACTIVE)
               VALUES
              (P.WHOLENAME, UPPER(P.ADUSERNAME), UPPER(P.ADUSERNAME), P.FIRST_NAME, P.LAST_NAME, P.ADUSERNAME, P.EMAIL, 
               P.PHONENUMBER, P."LOCATION_L7", P.DEPARTMENT, 'EMG', DECODE(UPPER(P.STATUS),'ACTIVE','Y','N'));
                 
COMMIT;

Open in new window

0
rleni59
Asked:
rleni59
2 Solutions
 
MilleniumaireCommented:
This is usually caused by attempting to perform multiple updates of the same row in the destination table:

    ORA-30926: unable to get a stable set of rows in the source tables
        Cause: A stable set of rows could not be got because of large dml
               activity or a non-deterministic where clause.
       Action: Remove any non-deterministic where clauses and reissue the dml.

It is possible, though, to aggregate the results in an inline view in the USING clause.
0
 
sventhanCommented:
0
 
rleni59Author Commented:
The final solution was to create a unique index on the LNOTES_EXP table. Once created it worked. Pretty much what the article was saying.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now