Oracle sql MERGE INTO with triple where clauses

Question

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

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
);

But I'm getting the below error message:

    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

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

Thanks for helping out!

try [this](https://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables), some issues described could match your problem. — Seyran, Jun 19 '17 at 10:26

score 1 · Answer 1 · answered Jun 19 '17 at 10:25

1

Merge scripts usually does lot of DML operations,I would rather suggest you to create a view of the second query instead of having filtering clause in merge statement. And, also there is where clause in source statement.

Creating views for source and destination, makes merge job easier

answered Jun 19 '17 at 10:25

Ven

2,011
1
13
27

How does any of this answer the OP's question? Perhaps you didn't understand the question, which was "**why is the error coming?**" – Jun 19 '17 at 11:54

APC · Answer 2 · 2017-06-19T11:55:00.693

1

"unable to get a stable set of rows in the source tables"

You get this error because your sub-query selects from SCHEMA_1.TABLE_1 which is the table you're merging into. We cannot do this: the MERGE INTO SCHEMA_1.TABLE_1 table1 statement means you're inserting or updating that table. So which version of its data should Oracle be returning in the sub-query?

You need to rewrite the sub-query in the USING clause so it captures the logic of the sub-query (minus the select on SCHEMA_1.TABLE_1, obviously). This will give you the values you need to refine the ON join or the MATCHED filter.

edited Jun 19 '17 at 11:55

answered Jun 19 '17 at 11:07

APC

144,005
19
170
281

The problem is coming from the `using` clause or `where table1.COLUMN_2 in ( select` clause? – Jun 19 '17 at 13:26
1

The WHERE clause in the WHEN matched. (Although the USING clause has a view, so *that* could be problematic too.) – APC Jun 19 '17 at 13:51
Yeah, but I need to use it to get the appropriate result, what more options do I have? – Jun 19 '17 at 14:07
What is point of the WHERE subquery? What you have posted here doesn't join `TABLE_1` to any other the other tables. Presumably just a mistake in the anonymisation. – APC Jun 19 '17 at 14:11
could you please suggest where to put the content of the said clause to not impede the execution (in the USING clause for example . . . ). – Jun 19 '17 at 14:19
I don't know your data model or your business logic so I can't do this for you. I'm afraid you'll have to figure out the specifics for yourself. – APC Jun 19 '17 at 14:21

Oracle sql MERGE INTO with triple where clauses

2 Answers2