0
MERGE INTO table1 t USING
  (SELECT '000004' AS SENDER,'Receiver' AS RECEIVER,'1030' AS IDENTIFIER,'2016' AS CREATIONDATEANDTIME,'2' AS ACKCODE,'Test' AS ACKDESCRIPTION
   FROM table1) s ON(t.SENDER = s.SENDER
                     AND t.IDENTIFIER = s.IDENTIFIER) WHEN MATCHED THEN
UPDATE
SET t.CREATIONDATEANDTIME = '1213',
    t.RECEIVER = 'hello' WHEN NOT MATCHED THEN
INSERT (t.SENDER,
        t.RECEIVER,
        t.IDENTIFIER,
        t.CREATIONDATEANDTIME,
        t.ACKCODE,
        t.ACKDESCRIPTION)
VALUES (s.SENDER,
        s.RECEIVER,
        s.IDENTIFIER,
        s.CREATIONDATEANDTIME,
        s.ACKCODE,
        s.ACKDESCRIPTION)

Output of query: scenario 1: When there is no data matching the condition(t.SENDER = s.SENDER and t.IDENTIFIER = s.IDENTIFIER), I get an error as follows ORA-30926: Unable to get 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 activity where clause. Action: Remove any non-deterministic where clause and reissue dml

Scenario 2: When there is data matching the condition (t.SENDER = s.SENDER and t.IDENTIFIER = s.IDENTIFIER) then in the table, I can see 5 new entries.

Can you please help

Mihai
  • 26,325
  • 7
  • 66
  • 81
user3384231
  • 3,641
  • 2
  • 18
  • 27
  • Since all your values are hardcoded,you get duplicates in the query after USING.The question is why hard code ALL values? – Mihai Feb 10 '16 at 16:48
  • this error means that there are duplicate values in your source select statement based off of the key you are using for the update. Are you literally trying to insert those hardcoded values in the select statement or is that just an example? – Jared Feb 10 '16 at 16:49
  • sagi: I was told from this user that its best to ask new question in new page it got messsy in previous page. So I created new question. @Mihai: I am testing this query first in the SQL worksheet and then once it starts working properly I will put this query in the application which is going to execute this query.. – user3384231 Feb 10 '16 at 17:19
  • Since all the values in your USING table are hard-coded values, I suggest that instead of saying `...FROM TABLE1` you should use `...FROM DUAL`. If you execute the SELECT in your `USING` clause in a query tool you'll find it returns one row for each row in TABLE1, when you only want a single row returned. Best of luck. – Bob Jarvis - Слава Україні Feb 10 '16 at 19:16

0 Answers0