SQL ORA-30926: 合并表时无法在源表中获得一组稳定的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21935340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-30926: unable to get a stable set of rows in the source tables when Merging tables
提问by Lucas Rezende
I have this Merge statement:
我有这个合并声明：
MERGE INTO TB_DP_REGIAO B
USING TMP_DP_REGIAO P
ON (P.DS_PROTHEUS_CODE = B.DS_PROTHEUS_CODE)
WHEN MATCHED THEN UPDATE SET B.DS_PLANNING_CODE = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DS_PLANNING_CODE ELSE B.DS_PLANNING_CODE END,
B.DT_LOAD = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DT_LOAD ELSE B.DT_LOAD END
WHEN NOT MATCHED THEN INSERT(B.DS_PROTHEUS_CODE, B.DS_PLANNING_CODE, B.DT_LOAD) VALUES(P.DS_PROTHEUS_CODE, P.DS_PLANNING_CODE, P.DT_LOAD);
That is returning me this error:
这给我带来了这个错误：
Error starting at line 1 in command:
MERGE INTO TB_DP_REGIAO B
USING TMP_DP_REGIAO P
ON (P.DS_PROTHEUS_CODE = B.DS_PROTHEUS_CODE)
WHEN MATCHED THEN UPDATE SET B.DS_PLANNING_CODE = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DS_PLANNING_CODE ELSE B.DS_PLANNING_CODE END,
B.DT_LOAD = CASE WHEN B.DT_LOAD < P.DT_LOAD THEN P.DT_LOAD ELSE B.DT_LOAD END
WHEN NOT MATCHED THEN INSERT(B.DS_PROTHEUS_CODE, B.DS_PLANNING_CODE, B.DT_LOAD) VALUES(P.DS_PROTHEUS_CODE, P.DS_PLANNING_CODE, P.DT_LOAD)
Error report:
SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
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.
When the target table is empty, it works. If I run it when the P.DT_LOADis the same as B.DT_LOAD, it works. When I run it next day, when the P.DT_LOADis one day ahead, I get this error.
当目标表为空时，它可以工作。如果我在 与P.DT_LOAD相同时运行它B.DT_LOAD，它会起作用。当我第二天运行它时，P.DT_LOAD即提前一天运行时，我收到此错误。
Can someone help me on this?
有人可以帮助我吗？
Thanks in advance!
提前致谢！
回答by Dmitry Nikiforov
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:
这是一个有点棘手的案例。主要原因是您似乎在 TMP_DP_REGIAO.DS_PROTHEUS_CODE 列中有重复项，并且 MERGE 尝试多次更新目标表的同一行。但是如果更新列中的新值和旧值相同，Oracle 可以跳过这个重复问题：
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:
但是，如果旧值和新值不同，Oracle 会引发您得到的异常：
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
回答by Pawan Rawat
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.
此问题的另一个原因也可能是 ON 子句中指定的条件。当分别有 1 对多映射到目标行与源行时会发生此错误，这可能是由于两个原因。
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.
在第二种情况下，必须修改 ON 子句条件以分别在目标表和源表中实现 1 对 1 或多对一映射。
