2

I am using the below query,

MERGE INTO table2 b
     USING (
         SELECT column1,column2,column3
         FROM table1
     ) a
     ON (a.column3 = 'UPDATE')
     WHEN NOT MATCHED THEN
         INSERT (b.column1, b.column2) VALUES (a.column1,a.column2)
     WHEN MATCHED THEN
         UPDATE SET a.column1 = b.column1,a.column2=b.column2;

When the table2 is empty it is running succesfully for the first time. If i am running for the second time even though table1 data isn't changed i am getting the error as unable to get a stable set of rows in the source tables..Is there a workaround for this error.

As mentioned in this thread , I tried putting the Distinct but it did not solve..

I think rowid should solve the purpose here..but not sure

Thanks

Community
  • 1
  • 1
javanoob
  • 6,070
  • 16
  • 65
  • 88
  • possible duplicate of [ORA-30926: unable to get a stable set of rows in the source tables](http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables) – DCookie Jul 03 '12 at 05:00
  • I tried the solution mentioned in that thread and it does not work..Not sure if i am doing something wrong – javanoob Jul 03 '12 at 05:16

1 Answers1

5

ON (a.column3 = 'UPDATE') is not sufficient.

You need to state how the two tables relate to each other. This is important so that:
- A given row in Table1 matches one row in Table2 (UPDATE)
- Or, a given row in Table1 has no match in Table2 (INSERT)

This means that the ON clause must match the two tables together 1:1 or 1:0. Never many:many. In the code below I will assume that column1 is a unique identifier. If that's the case it conforms to the 1:1 requirement.

If you don't have a way to uniquely identify each row in each table, then you need to change your data so that you can.

MERGE INTO
  table2 b
USING (
  SELECT column1,column2,column3
  FROM table1
) a
ON (
  b.column1 = a.column1
)
WHEN NOT MATCHED THEN
  INSERT (b.column1, b.column2) VALUES (a.column1,a.column2)
WHEN MATCHED THEN
  UPDATE SET b.column2 = a.column2;

Note that I don't use the UPDATE value here, as it doesn't seem necessary (You may provide more info which explains why it is).

Instead, for every row in table1, this searches for a row in table1 with the same value in column1. If one is NOT found, the INSERT is executed. If one IS found, then the update is executed.


What yours had been doing is saying that a match is found if (a.column3 = 'UPDATE'). This meant that every row in table2 was being matched against every 'UPDATE' row in table1 - making it try to update the same row in table2 with the values from many rows in table1.

Then, also, every row where a.column3 <> 'UPDATE' would be inserted into table2.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • I was about to write that 0:1 matching should also be mentioned when I decided to verify myself if Oracle MERGE had a WHEN NOT MATCHED BY SOURCE clause. I was surprised it turned out not to have one, all the more so since the [doc page](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm) says that the statement *‘lets you avoid multiple `INSERT`, `UPDATE`, **and `DELETE`** DML statements’*. But it goes on to reveal that there's actually no DELETE clause in Oracle MERGE! I wonder if you know of something I might be missing here. Otherwise the docs seem to be contradictory – Andriy M Jul 03 '12 at 06:56
  • @AndriyM there is a delete clause but it seems to be part of the UPDATE section and I've found it to be rather limited usefulness anyway. – Mike Meyers Jul 03 '12 at 09:22
  • @MikeMeyers: Ah, I overlooked that all right. Shows how carefully I was reading the article ("skimming it over" more likely). Thank you! – Andriy M Jul 03 '12 at 09:26
  • Thanks so much Dems, i figured out what is common between two tables and solved the problem.. – javanoob Jul 03 '12 at 20:28