We will get this when we are trying to update a row in a table, for which there will be two or more corresponding records.
Table 1:
| tc11 | tcl2 |
|---|---|
| t1 | a |
| t2 | b |
| t3 | c |
| t4 | d |
Table 2:
| tc21 | tc22 |
|---|---|
| b1 | a |
| b2 | b |
| b3 | c |
| b4 | d |
Table 3:
| tc31 | tc32 |
|---|---|
| t1 | b1 |
| t2 | b2 |
| t2 | b3 |
| t4 | b4 |
We will get error, when we try to update TABLE1 by using TABLE3 as follows:
MERGE INTO table1 t1
USING table3
ON (t1.tc11=t3.t31)
WHEN MATCHED THEN
UPDATE SET tc12=tc32;
Analysis:
It is because, for one record in TABLE1, we are getting multiple corresponding values in TABLE3 (i.e. for value “t2”, we are getting “b2” and “b3”).
So when the above query try to update TABLE1 based on “t2”, it cannot decide which value should be updated either “b2” or “b3”, there it will throw the “ORA-30926 unable to get a stable set of rows in the source tables” error.
Solution:
Add few more conditions in WHERE clause, so that you can avoid such ambiguous situations.