ORA-30926: unable to get a stable set of rows in the source tables when Merging tables ORA-30926:在合并表时无法获取源表的稳定行集。
ORA-30926: unable to get a stable set of rows in the source tables when Merging tables ORA-30926:在合并表时无法获取源表的稳定行集。
我有这个合并语句:\n
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);
\n这给我返回了以下错误:\n
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.
\n当目标表为空时,它是有效的。如果我在P.DT_LOAD
与B.DT_LOAD
相同时运行它,它也是有效的。当我在第二天运行它时,当P.DT_LOAD
比B.DT_LOAD
提前一天时,我会得到这个错误。\n有人可以帮我解决这个问题吗?\n提前谢谢!
在使用MERGE命令合并表时,出现ORA-30926错误,错误信息为"unable to get a stable set of rows in the source tables"。该错误的主要原因是TMP_DP_REGIAO.DS_PROTHEUS_CODE列中存在重复的值,并且MERGE命令尝试多次更新目标表的同一行。但是,如果更新列的新值和旧值相同,Oracle可以跳过重复值的问题。
解决该问题的方法是在MERGE命令中使用条件语句,确保更新列的新值和旧值不相同。下面是一个示例:
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 /
当新值和旧值不同时,执行上述代码会报错,错误信息为"ORA-30926: unable to get a stable set of rows in the source tables"。
以上就是ORA-30926错误出现的原因以及解决方法,在使用MERGE命令合并表时,需要注意避免目标表中存在重复值的情况,以确保操作的稳定性。