Issue while updating column using Merge statement

Question

I need to update a column based on the inner join of two tables in oracle. I am using Merge as the inner join doesn't support in Update.

Query :

MERGE INTO  FAC.CMC_PRSQ_SITE_QA PRSQ   
USING(
       SELECT PRPR_ID, ADDRESS_TYPE, PRAD_EFF_DT, PRAD_TERM_DT
       FROM FSG_WRK.FSG_PRAD_PRSQ_TEMP 
     ) TEMP
     ON (TEMP.PRPR_ID = PRSQ.PRPR_ID
     AND TEMP.ADDRESS_TYPE = PRSQ.PRAD_TYPE
     AND TEMP.PRAD_EFF_DT = PRSQ.PRAD_EFF_DT)
WHEN MATCHED THEN UPDATE 
SET PRSQ.PRSQ_NEXT_VER_DT = TEMP.PRAD_TERM_DT
WHERE TEMP.PRAD_TERM_DT > PRSQ.PRSQ_LAST_VER_DT ;

But getting below error :

ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 2
30926. 00000 -  "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.

Also, please let me know if there is any other option to rewrite this query.

Do you have multiple temp rows for a single ID/type/date? If so what should happen? Sample data (which reproduces the error) and expected results would be useful. — Alex Poole, Jan 11 '19 at 15:35
You may want to take a look at this question: https://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables — eaolson, Jan 13 '19 at 01:00

score 1 · Answer 1 · answered Jan 13 '19 at 04:12

Does your source query (TEMP) return more than 1 instance of a row in your destination (PRSQ) that matches your merge conditions? You typically see this type of error when the source contains more than 1 instance of a row that needs to be updated in the destination. If it does you need to adjust either your source query or your merge condition to avoid trying to merge multiple rows from your source into the same row of your destination.

Issue while updating column using Merge statement

1 Answers1