Oracle sql MERGE INTO with double where clauses

Question

I have the following SQL code (this is how much I made so far):

MERGE INTO SCHEMA_1.TABLE_1 table1
USING
(

SELECT *


FROM  SCHEMA_2.TABLE_2 table2
LEFT JOIN SCHEMA_2.VIEW_1 view1
ON table2.COLUMN_4 = view1.COLUMN_1

)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 am getting the following error message:

SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

Why is this error coming? Where to change the code to make it work?

Thanks for help!

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. — , Jun 19 '17 at 10:05

score 0 · Accepted Answer · answered Jun 19 '17 at 07:30

0

Well, you have to remove the ORDER BY from the UPDATE statement. I do not have your table structure and data, try as

MERGE INTO schema_1.table_1 table1
     USING (SELECT *
              FROM    schema_2.table_2 table2
                   LEFT JOIN
                      schema_2.view_1 view1
                   ON table2.column_4 = view1.column_1) 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)

answered Jun 19 '17 at 07:30

Jacob

14,463
65
207
320

But is the sql correct, I mean it will only update the rows that are limited by the last where clause? – Jun 19 '17 at 08:11
@HumanBeing Well without table structure and sample data, it would be difficult. But the error which you were having is due to the `ORDER BY` clause. – Jacob Jun 19 '17 at 08:13
@HumanBeing `table1.COLUMN_19` is not part of the select list. – Jacob Jun 19 '17 at 08:27
@HumanBeing Most importantly `ORDER BY` in a subquery is not allowed. – Jacob Jun 19 '17 at 08:28
now I get this error: 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. – Jun 19 '17 at 10:04
@HumanBeing Have a look at [this](https://stackoverflow.com/a/2337465/599528) I suggest you create another question for this as it is a different error. – Jacob Jun 19 '17 at 10:07

Oracle sql MERGE INTO with double where clauses

1 Answers1