Oracle multivalue groups

Question

I have a table with 2 multi value fields for example:

1st row contains these fields:

field 1: 1,4,5

field 2: 1,2,3

second row contains:

field 1: 5,6,7

field 2: ,6,3

third row:

field 1: 8,,9

field 2: 1,,3

|---------------------|------------------|
|      column 1       |     column 2     |
|---------------------|------------------|
|       1,4,5         |       1,2,3      |
|---------------------|------------------|
|       5,6,7         |       ,6,3       |
|---------------------|------------------|
|       8,,9          |        1,,3      |
|---------------------|------------------|

So both fields are in relation- the value in position 2 of field 1 is related to the value in position 2 of field2. The thing that I want to do is to update/replace one or more of the values in field 2 only for which the values in each group are equal.

For example in first row I have matching value '1' in position 0 which need to be replaced in field 2. How I could do that? Just to mention that null values from field 1 and field 2 should not be considered as equal, they are there just to show position. Ask if you need some more info

This is a horrendous table design. Please try to move away from storing comma separated values in your tables. The problem here is that it will be very difficult for you to do what you have in mind given your current design. Instead, consider storing each CSV value in a separate row, and maybe add a new column to keep track of to which group each CSV value belongs. — Tim Biegeleisen, Feb 20 '19 at 10:34

score 0 · Accepted Answer · answered Feb 20 '19 at 11:18

Firstly, don't use this; instead fix your table design to not be storing comma-separated values. However, if you insist then you can use this to split the csv into individual values then perform the replacement and then aggregate them again and use a MERGE statement to update the table correlating the update on the ROWID pseudocolumn.

Oracle Setup:

CREATE TABLE test_data ( column1 VARCHAR2(20), column2 VARCHAR2(20) );

INSERT INTO test_data ( column1, column2 )
SELECT '1,4,5', '1,2,3' FROM DUAL UNION ALL
SELECT '5,6,7', ',6,3'  FROM DUAL UNION ALL
SELECT '8,,9', '1,,3'   FROM DUAL UNION ALL
SELECT '1,4,5', '3,4,5' FROM DUAL;

Perform Replacement:

This adds 10 to each column2 value where it matches column1.

MERGE INTO test_data dst
USING ( 
  SELECT rid,
         REPLACE(
           LISTAGG(
             COALESCE( TO_CHAR( column1 ), '#' ),
             ','
           ) WITHIN GROUP ( ORDER BY idx ),
           '#'
         )AS column1,
         REPLACE(
           LISTAGG(
             COALESCE( TO_CHAR( CASE column2 WHEN column1 THEN column2 + 10 ELSE column2 END ), '#' ),
             ','
           ) WITHIN GROUP ( ORDER BY idx ),
           '#'
         ) AS column2
  FROM   (
    SELECT t.ROWID AS rid,
           COLUMN_VALUE AS idx,
           TO_NUMBER( REGEXP_SUBSTR( t.column1, '(\d*)(,|$)', 1, COLUMN_VALUE, NULL, 1 ) ) AS column1,
           TO_NUMBER( REGEXP_SUBSTR( t.column2, '(\d*)(,|$)', 1, COLUMN_VALUE, NULL, 1 ) ) AS column2
    FROM   test_data t
           CROSS JOIN
           TABLE(
             CAST(
               MULTISET(
                 SELECT LEVEL
                 FROM   DUAL
                 CONNECT BY LEVEL < REGEXP_COUNT( t.column1, '(\d*)(,|$)' )
               )
               AS SYS.ODCINUMBERLIST
             )
           ) l
  )
  GROUP BY rid
) src
ON ( dst.ROWID = src.RID )
WHEN MATCHED THEN
  UPDATE SET column2 = src.column2 WHERE src.column2 <> dst.column2;

Output:

SELECT * FROM test_data;

COLUMN1 | COLUMN2
:------ | :------
1,4,5   | 11,2,3 
5,6,7   | ,16,3  
8,,9    | 1,,3   
1,4,5   | 3,14,15

db<>fiddle here

Did it like you said, but I have a problem. When I have fields [1,1] and [2,2] after the merge it somehow removes the column2 second value like distinct and it updates it to [1,1] [2]. So I lose the multi value group like that. And will it return [1,2,3] [null,5] if I got for example [1,2,3] [null, null, 5]. I shouldn't lose the multi value groups. Anyway it works. EDIT: [1,1] and [2,2] are the two fields — Beyond1337, Feb 20 '19 at 12:28
@Beyond1337 [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=6ebd2b7a195c615f5fe81bbc632795a3) I added those two cases `1,1 | 2,2` and `1,2,3 | ,,5` and cannot replicate your issue. Please can you create an example to demonstrate the problem. — MT0, Feb 20 '19 at 12:32
Yes, I tried it too and it works. I messed up my REGEXP_COUNT regex. The count was always 1, so when I have update it was replacing the value only with the first group of the field. It works as expected now, tnx — Beyond1337, Feb 20 '19 at 12:58

Oracle multivalue groups

1 Answers1