CODEWITHSUNDEEP
sqloracle-database
Erwin
Erwin

Reputation: 61

Merge into gives error ORA-30926: unable to get a stable set of rows in the source tables

I try to run the next 2 queries sequentially. The first one runs perfectly, the second one throws

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

I searched the net for a solution but I can't replicate it for my queries. Can anyone help me please?

Query 1:

merge into sdc_compare_person dcip
using (
select anumber, position, character
from sdc_diakrietposities_cip
where kind = 'Surname'
) x
on (dcip.sourcekey = x.anumber)
when matched then update set
dcip.GESVOR = substr(dcip.GESVOR, 1, x.position - 1) ||
                x.character ||
                substr(dcip.GESVOR, x.position + 1, length(dcip.GESVOR)-x.position)
;

188 rows merged.

Query 2:

merge into sdc_compare_person dcip
using (
select anumber, position, character
from sdc_diakrietposities_cip
where kind = 'Lastname'
) x
on (dcip.sourcekey = x.anumber)
when matched then update set
dcip.GESNAM_D = substr(dcip.GESNAM_D, 1, x.position - 1) ||
                x.character ||
                substr(dcip.GESNAM_D, x.position + 1, length(dcip.GESNAM_D) - x.position)
;

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

Upvotes: 1

Views: 8508

Answers (2)

wolfrevokcats
wolfrevokcats

Reputation: 2100

From the comments to the question it becomes clear that the author wants to update the same record many times.
Of course, this cannot get past ORA-30926 when trying to do it by a merge construct.
It's hard or impossible to do such a thing in pure oracle sql, but it's easily done with a pl/sql function.
For example:

create or replace function replace_chars(p_str varchar2, p_id number, p_kind varchar2) return varchar2 as
                                l_str varchar2(32767):=p_str;
begin
    for u in (select u.position, u.character  from sdc_diakrietposities_cip u 
                  where u.anumber=p_id and u.kind=p_kind order by u.position) loop
        if (u.position >= 1 or u.position <= length(l_str)) then
            l_str:=substr(l_str, 1, u.position-1)|| u.character || substr(l_str, u.position+1);
        end if;
    end loop;
    return l_str;
end;

Use like this:

update sdc_compare_person t 
set t.GESNAM_D= replace_chars(t.GESNAM_D, t.sourcekey, 'Lastname');

I'd suggest backing up your table before running this.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

You can alway use ordinary update, it's not so elegant as MERGE, but should work:

UPDATE sdc_compare_person dcip
SET dcip.GESNAM_D = (
    SELECT substr(dcip.GESNAM_D, 1, x.position - 1) ||
           x.character ||
           substr(dcip.GESNAM_D, x.position + 1, length(dcip.GESNAM_D) - 
                  x.position)
    FROM sdc_diakrietposities_cip x
    where kind = 'Lastname'
      AND dcip.sourcekey = x.anumber
)
WHERE dcip.sourcekey  IN (
   select anumber
   from sdc_diakrietposities_cip
   where kind = 'Lastname'
);

Upvotes: 2

Related Questions