Oracle sql MERGE INTO with三重where子句

Question

我有以下SQL代码(这是我到目前为止得到的代码)：

MERGE INTO SCHEMA_1.TABLE_1 table1
USING
(

SELECT DISTINCT table2.COLUMN_5, 
    view1.COLUMN_6 

FROM  SCHEMA_2.TABLE_2 table2
LEFT JOIN SCHEMA_2.VIEW_1 view1
ON table2.COLUMN_4 = view1.COLUMN_1
WHERE
   view1.COLUMN_6 is not null
)t2
ON(table1.COLUMN_2 = t2.COLUMN_5)
WHEN MATCHED THEN UPDATE SET
table1.COLUMN_3 = t2.COLUMN_6
where table1.COLUMN_2 in
(
select  

    table1.COLUMN_2
    from 
        SCHEMA_1.TABLE_1 table1, 
    SCHEMA1.TABLE_3 table3, 
    SCHEMA1.TABLE_4 table4,
    SCHEMA1.TABLE_5 table5,
    SCHEMA1.TABLE_6 table6,
    SCHEMA1.TABLE_7 table7
    where 
        table4.COLUMN_7 = table3.COLUMN_8 and
    table5.COLUMN_9 = table4.COLUMN_10 and
    table5.COLUMN_11 = table1.COLUMN_12 and
    table4.COLUMN_13 = table7.COLUMN_14 and
    table7.COLUMN_15 = table6.COLUMN_16 and 
    table6.COLUMN_17 = 'DOL' and
    table4.COLUMN_18 = 1 and 
    table1.COLUMN_2 is not null and 
    table1.COLUMN_3 is null
    order by 
        table1.COLUMN_19 desc
);

但我收到了以下错误消息：

    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

是什么导致了这个错误？在代码中的哪些地方进行更改才能使其正常工作？

谢谢你的帮助！

Answer 1

“无法在源表中获取稳定的行集”

之所以会出现这个错误，是因为您的子查询从要合并到的表SCHEMA_1.TABLE_1中进行选择。我们不能这样做：MERGE INTO SCHEMA_1.TABLE_1 table1语句意味着您正在插入或更新该表。那么，Oracle应该在子查询中返回哪个版本的数据呢？

您需要在USING子句中重写子查询，以便它捕获子查询的逻辑(显然，去掉了SCHEMA_1.TABLE_1上的select )。这将为您提供细化ON连接或MATCHED筛选器所需的值。

Answer 2

合并脚本通常会做很多DML操作，我建议您创建第二个查询的视图，而不是在Merge语句中使用过滤子句。而且，在source语句中还有where子句。

为源和目标创建视图，使合并作业更容易