ORA-30926: unable to get a stable set of rows in the source tables when Merging tables
It's a bit tricky case. The primary reason is that you seems have duplicates in TMP_DP_REGIAO.DS_PROTHEUS_CODE column and MERGE tries to update the same row of destination table several times. But if new values and old values in updated columns are the same, Oracle can skip this issue of duplicates:
SQL> select * from t; CODE TEXT ---------- ---------- 1 test SQL> merge into t using ( 2 select 1 code,'test' text from dual union all 3 select 1 code,'test' text from dual 4 ) s 5 on (t.code = s.code) 6 when matched then 7 update set t.text = s.text 8 /2 rows merged
But if old and new values are different Oracle raises the exception you get:
SQL> merge into t using ( 2 select 1 code,'a' text from dual union all 3 select 1 code,'a' text from dual 4 ) s 5 on (t.code = s.code) 6 when matched then 7 update set t.text = s.text 8 /merge into t using ( *error in line 1:ORA-30926: unable to get a stable set of rows in the source tables
Another reason for this problem could also be the conditions specified in ON clause. This error occurs when there is 1 to many mapping to your destination rows vs source rows respectively which can be due to two reasons.
1) there are duplicate rows in source table.2) there are unique rows in source table, but ON clause conditions are pointing to multiple rows in the source table.
In second case the ON clause conditions has to modified to achieve 1 to 1 or many to one mapping in destination and source table respectively.