带有单个where子句的Oracle sql MERGE INTO
发布于 2021-05-10 20:55:53
我有以下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
是什么导致错误?在哪里更改代码以使其起作用?
感谢您的帮助!
关注者
0
被浏览
92
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;