Oracle sql MERGE INTO with a single where clause

Question

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;

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?

Thanks for helping out!

Answer 1

For this example your problem is definitely in the USING subquery. This query produces more than one value of table2.column1:

SELECT DISTINCT table2.column1, 
        view1.column2
 FROM SCHEMA2.TABLE_2 table2
 LEFT JOIN SCHEMA2.VIEW_1 view1
 ON table2.column2 = view1.column3

So the ON clause will match the same row(s) in table1 more than once:

ON (table1.column3 = t2.column1 )

Oracle cannot figure out which value of t2.column2 should be used in the UPDATE, so it hurls ORA-30926.

Using distinct in the subquery doesn't help because that gives permutations of all the columns. You need to write a subquery which will produce unique values of t2.column1 across all rows, or add another identifying column(s) to generate a unique key you can join to table1.

Answer 2

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;