1

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
Erwin
  • 61
  • 1
  • 8
  • Add: This query updates the column 'dcip.gesnam_d' with special characters (from column 'x.character'). So X contains several duplicate 'anumber'-values because some 'dcip_gesnam_d'-values could have more then 1 special character. – Erwin Sep 04 '17 at 09:47
  • Maybe duplicate of https://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables? – Tony Andrews Sep 04 '17 at 09:48
  • Yeah that's right but with the given answers I can't produce a working solution. It keeps throwing the ORA-30926-error. Query 2 is the same query as Query 1, I don't understand why 2 works and 1 does not. – Erwin Sep 04 '17 at 11:33
  • In your query 2 the using subquery produces two records with the same anumber – wolfrevokcats Sep 04 '17 at 12:41
  • One query works and the other does not. But _both queries_ got subqueries that produces two or more records with the same number. – Erwin Sep 04 '17 at 12:56
  • 1. Please, run the query below and tell us the results. `select kind,count(*) c1,count(distinct anumber) c2 from sdc_diakrietposities_cip where kind in ('Surname','Lastname') group by kind;` 2. I guess, sourcekey is a unique/primary key? – wolfrevokcats Sep 04 '17 at 13:46
  • 1. The results are: Surname-260-247 and Lastname-760-673. 2. Yes, sourcekey is a unique key. – Erwin Sep 04 '17 at 14:03
  • Thank you! Can you run a slightly modified version? (sorry, it's getting uglier): `select c.kind,count(*) c1,count(distinct c.anumber) c2 from sdc_diakrietposities_cip c join sdc_compare_person p on c.anumber=p.sourcekey where c.kind in ('Surname','Lastname') group by c.kind` – wolfrevokcats Sep 04 '17 at 14:14
  • @wolfrevokcats: **sdc_diakrietposities_cip** looks like this: ||ANUMBER;POSITION;CHARACTER;KIND || 713918;5;ć;Lastname||895086;1;Ć;Lastname || 895086;7;ć;Lastname || -- Based on x.ANUMBER sdc_compare_person x gets updated with a special character (x.CHARACTER) on a specific position (x.POSITION). In some case this has to happen more than 1 time. So I can't use a distinct-command around x.ANUMBER in de using-clause. – Erwin Sep 04 '17 at 14:20
  • New result: Surname-88-85, Lastname-190-166. – Erwin Sep 04 '17 at 14:22
  • Well, I think I've understood what you are trying to do. That needs some thinking over... – wolfrevokcats Sep 04 '17 at 15:27
  • Erwin, I added my answer, and I think you should edit your post to reflect the fact that you actually want to be able to update the same field multiple times. This is crucial for understanding the question. – wolfrevokcats Sep 04 '17 at 17:02

2 Answers2

2

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'
);
krokodilko
  • 35,300
  • 7
  • 55
  • 79
1

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.

wolfrevokcats
  • 2,100
  • 1
  • 12
  • 12
  • Thanx @wolfrevokcats, this was the solution I was searching for. Despite my terrible problem description... apologies for that. – Erwin Sep 05 '17 at 14:19