带有单个where子句的Oracle sql MERGE INTO

小编典典

带有单个where子句的Oracle sql MERGE INTO

sql

我有以下SQL代码（这是到目前为止我得到了多少）：

 MERGE INTO SCHEMA1.TABLE_1 table1 USING
    (
     SELECT DISTINCT table2.column1, 
            view1.column2
     FROM SCHEMA2.TABLE_2 table2
     LEFT JOIN SCHEMA2.VIEW_1 view1
     ON table2.column2 = view1.column3

    ) t2 ON (table1.column3 = t2.column1 )

    WHEN MATCHED THEN
      UPDATE
      SET table1.column4 = t2.column2;

以下是VIEW_1的定义：

    CREATE VIEW SCHEMA_2.VIEW_1
AS (SELECT 
SCHEMA_2.TABLE_1.COLUMN_1, 
SCHEMA_2.TABLE_2.COLUMN_1,
SCHEMA_2.TABLE_2.COLUMN_2,
SCHEMA_2.TABLE_2.COLUMN_3,
SCHEMA_2.TABLE_5.COLUMN_1, 
SCHEMA_2.TABLE_6.COLUMN_1, 
SCHEMA_2.TABLE_6.COLUMN_2,
SCHEMA_2.TABLE_6.COLUMN_3,
SCHEMA_2.TABLE_6.COLUMN_4, 
SCHEMA_2.TABLE_7.COLUMN_1, 
SCHEMA_2.TABLE_7.COLUMN_2, 
SCHEMA_2.TABLE_8.COLUMN_1 
FROM SCHEMA_2.TABLE_1
INNER JOIN SCHEMA_2.TABLE_2
ON SCHEMA_2.TABLE_1.COLUMN_1 = SCHEMA_2.TABLE_2.COLUMN_2 
INNER JOIN SCHEMA_2.TABLE_5
ON SCHEMA_2.TABLE_1.COLUMN_4 = SCHEMA_2.TABLE_5.COLUMN_3
LEFT OUTER JOIN SCHEMA_2.TABLE_6
ON SCHEMA_2.TABLE_2.COLUMN_2 = SCHEMA_2.TABLE_6.COLUMN_4
LEFT OUTER JOIN SCHEMA_2.TABLE_7
ON SCHEMA_2.TABLE_2.COLUMN_1 = SCHEMA_2.TABLE_8.COLUMN_5
);

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

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

是什么导致错误？在哪里更改代码以使其起作用？

感谢您的帮助！

阅读 225

2021-04-28

共1个答案

小编典典

以我的经验，不仅在USING子句为MATCH表中的一行返回一行以上时，而且在无法确定
仅返回一行（即使没有实际的行）时，也会返回此错误。返回多行的情况）。为了在这种情况下强制解析器接受查询，我通常会在MATCH..ON列上使用GROUP
BY。

MERGE INTO SCHEMA1.TABLE_1 table1 USING
(
 SELECT table2.column1, 
        MAX(view1.column2) as column2
 FROM SCHEMA2.TABLE_2 table2
 LEFT JOIN SCHEMA2.VIEW_1 view1
 ON table2.column2 = view1.column3
 GROUP BY table2.column1
) t2 ON (table1.column3 = t2.column1 )

WHEN MATCHED THEN
  UPDATE
  SET table1.column4 = t2.column2;

2021-04-28