Oracle 12c updating multiple rows with the same data

I’m trying to write a script that pulls out all of the distinct ‘words’ in a string and saves them in another field. I’ve gotten the process to work in Oracle 19c (though any advice is welcome) but when I run the script in 12c the first record is correct but all the following records have the same data and I’m not sure what I’m doing wrong.

Thanks!

drop table temp purge;
create table temp (A CHAR(1), S1 varchar(32), S2 varchar(32));
commit;

insert into temp (A,S1)
select 'A', '1 2 3 4 1 2 3 4 1 2 3 4' from dual;
commit;
insert into temp (A,s1)
select 'B', '6 7 8 9 6 7 8 9 6 7 8 9 6 7 8 9' from dual;
commit;
insert into temp (A,s1)
select 'C', 'A B C D A B C D' from dual;
commit;

select * from temp;

UPDATE temp set (S2) = (
    SELECT LISTAGG(str, ' ') WITHIN GROUP (ORDER BY str) str
    FROM (
        SELECT DISTINCT REGEXP_SUBSTR(S1, '[^ ]+', 1, LEVEL) AS str FROM dual
        CONNECT BY REGEXP_SUBSTR(S1, '[^ ]+', 1, LEVEL) IS NOT NULL
    )
);


select * from temp;

output:

A S1                               S2
- -------------------------------- --------------------------------
A 1 2 3 4 1 2 3 4 1 2 3 4
B 6 7 8 9 6 7 8 9 6 7 8 9 6 7 8 9
C A B C D A B C D


3 rows updated.


A S1                               S2
- -------------------------------- --------------------------------
A 1 2 3 4 1 2 3 4 1 2 3 4          1 2 3 4
B 6 7 8 9 6 7 8 9 6 7 8 9 6 7 8 9  1 2 3 4
C A B C D A B C D                  1 2 3 4

expected:

A S1                               S2
- -------------------------------- --------------------------------
A 1 2 3 4 1 2 3 4 1 2 3 4          1 2 3 4
B 6 7 8 9 6 7 8 9 6 7 8 9 6 7 8 9  6 7 8 9
C A B C D A B C D                  A B C D

Answer

I don’t know what the problem is, but here is an alternative solution that avoids the CPU penalty of using REGEXP:

update temp set s2 =
xmlcast(
  xmlquery(
    'string-join(distinct-values(tokenize($X, " ")), " ")'
    passing s1 as X returning content
  )
  as varchar2(64)
);

Advertisement

Answer