Error: ORA-30926: unable to get a stable set of rows in the source tables

Question

I am getting the error : unable to get a stable set of rows in the source tables when I ran the below statement.

   merge into table_1 c
    using (select rep_nbr, T_nbr, SF from table_2) b
    on (c.rep_id=b.rep_nbr)
    when matched then
    update set 
    c.T_ID =b.T_nbr,
    c.SF=b.SF
    when not matched then
    insert(T_id, SF)
    values(null, null);

When i put distinct before the rep_nbr

merge into table_1 c
    using (select distinct rep_nbr,t_nbr,SF from table_2) b
    on (c.rep_id=b.rep_nbr)
    when matched then
    update set 
    c.T_ID =b.T_nbr,
    c.SF=b.SF
    when not matched then
    insert(T_id, SF)
    values(null, null);

ORA-01400: cannot insert NULL into c.rep_id. I don't want to populate c.rep_id, i just need them to match and then update those records that matched.

So what prevents you to remove the last segment of the query?When not matched.... — Mihai, Nov 24 '13 at 23:02
@mihai I guess it is not necessary to have that part. Will taking it out make a difference though? — JohnD, Nov 24 '13 at 23:10
If you need just the update yes.In your last query the prob is probably inserting null values into not null columns. — Mihai, Nov 24 '13 at 23:13
@mihai I took it out, now it says unable to get a stable set of rows — JohnD, Nov 24 '13 at 23:19
@mihai With distinct or without, I receive the same message. — JohnD, Nov 24 '13 at 23:32
See if this is of any help http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables — Mihai, Nov 24 '13 at 23:38

score 0 · Accepted Answer · answered Nov 25 '13 at 07:48

If I understood correctly, you don't need MERGE, just an UPDATE. Something like this should work:

UPDATE table_1 t1 
SET t1.t_id = (SELECT DISTINCT t2.t_nbr FROM table_2 t2 WHERE t1.rep_id = t2.rep_nbr),
    t1.sf = (SELECT DISTINCT t2.sf FROM table_2 t2 WHERE t1.rep_id = t2.rep_nbr),
WHERE t1.rep_id IN (SELECT rep_nbr FROM table_2)

But, however, in later versions of Oracle (from 10th or 11th) you can take out parts of merge, for example, take out WHEN NOT MATCHED part.

score 0 · Answer 2 · answered Nov 25 '13 at 13:22

0

remove part "with not matched then"

merge into table_1 c
    using (select distinct rep_nbr,t_nbr,SF from table_2) b
    on (c.rep_id=b.rep_nbr)
    when matched then
    update set 
    c.T_ID =b.T_nbr,
    c.SF=b.SF

answered Nov 25 '13 at 13:22

Galbarad

461
3
16

Error: ORA-30926: unable to get a stable set of rows in the source tables

2 Answers2