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

Updating data from one table with data from another in Oracle

The best way to do this is to use the merge syntax:

mergeintotab1

usingtab2

on(tab1.id=tab2.id)

whenmatchedthen

updatesettab1.val=tab2.val

Similarly, if there are multiple rows in tab2 corresponding to one in tab1, an error will occur: ORA-30926: unable to get a stable set of rows in the source tables
For example, insert into tab2 values(2,’xxxx’) in tab2

You can avoid this error by filtering the duplicate rows in the subquery in using,

merge:

mergeintotab1

using(select*FROMtab2XWHEREX.ROWID=

(SELECTMAX(Y.ROWID)FROMtab2YWHEREX.ID=Y.ID))tab2

on(tab1.id=tab2.id)

whenmatchedthen

updatesettab1.val=tab2.val

But considering the efficiency of the correlation query, you can use the oracle analysis function to de-duplicate and then merge

MERGE INTO YTRPT.T_EXP_CHECK_STATION P
USING (SELECT ORG_CODE, WAYBILL_NO
FROM (SELECT K.ORG_CODE,
K.WAYBILL_NO,
ROW_NUMBER() OVER(PARTITION BY K.WAYBILL_NO ORDER BY K.CREATE_TIME DESC) AS RW –Remove the duplicate to get out the latest record, otherwise merge will error
FROM YTEXP.T_EXP_OP_RECORD_HANDON K
WHERE K.CREATE_TIME >= P_START_TIME
AND K.CREATE_TIME < P_END_TIME + 10
AND K.OP_CODE IN (‘710’, ‘711’)
AND K.STATUS > 0
AND K.AUX_OP_CODE <> ‘DELETE’)
WHERE RW = 1) TEMP

ON (P.WAYBILL_NO = TEMP.WAYBILL_NO AND P.RPT_DATE = P_START_TIME)
WHEN MATCHED THEN
UPDATE SET P.SIG_ORG_CODE = TEMP.ORG_CODE;

Similar Posts: