带有单个where子句的Oracle sql MERGE INTO [英] Oracle sql MERGE INTO with a single where clause

问题描述

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

I have the following SQL code (this is how much I've got so far):

 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的定义:

The following is the definition of 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
);

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

But I'm getting the below error message:

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

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

What causes the error? Where to change in the code to make it work?

感谢您的帮助！

推荐答案

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

In my experience, this error is returned, not only when the USING clause returns more than one row for a row in the MATCH table, but also frequently when it cannot be sure that only one row will be returned (even if there are no actual cases of multiple rows being returned). To force the parser to accept the query in cases like this, I usually resort to using a GROUP BY on the MATCH..ON column(s).

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;

这篇关于带有单个where子句的Oracle sql MERGE INTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！