Demonstration of the error
Create two
tables and populate them with a few records.
create schema authorization rene
create table tq84_dst (id number, txt varchar2(5))
create table tq84_src (id number, txt varchar2(5))
;
begin
insert into tq84_dst (id) values (1);
insert into tq84_dst (id) values (2);
insert into tq84_dst (id) values (3);
insert into tq84_dst (id) values (4);
insert into tq84_src values (1, 'one');
insert into tq84_src values (2, 'two');
insert into tq84_src values (3, 'three');
end;
/
The following statement runs without problems and merges three records from the source table (tq84_src) into the destination table (tq84_dst):
merge into tq84_dst dst
using tq84_src src
on ( dst.id = src.id)
when matched then update
set dst.txt = src.txt;
If we add two records for id=4 into tq84_src…
insert into tq84_src values (4, 'four');
insert into tq84_src values (4, 'FOUR');
…the merge statement is not deterministic, i. e. it is not clear whether the value if the destination table's record where id=4 should be updated to four or FOUR.
So, when running the merge statement again, it produces ORA-30926: unable to get a stable set of rows in the source tables.
However, when the record with id=4 is deleted in tq84_dst, the statement runs again (but will only merge two records, obviously).
Special circumstance where the error is not thrown
Records in the source that already have the same value as that found in the destination don't seem to be counted as having updated the destination record, the following sample doesn't, unexepectedly to me, throw the error:
drop table tq84_src;
drop table tq84_dst;
create table tq84_src (
id integer,
val varchar2(10)
);
create table tq84_dst (
id integer,
val varchar2(10)
);
begin
insert into tq84_dst (id, val) values (1, 'one' );
insert into tq84_src (id, val) values (1, 'one' );
insert into tq84_src (id, val) values (1, 'one' );
insert into tq84_src (id, val) values (1, 'one' );
insert into tq84_src (id, val) values (1, 'ONE' );
end;
/
merge into tq84_dst d
using
tq84_src s
on
( s.id = d.id )
when
matched then update set d.val = s.val;
select * from tq84_dst;
It's unclear to me if this behavior is specified or a bug.