CODEWITHSUNDEEP
sqloracle-database
Gaurav Thuckral
Gaurav Thuckral

Reputation: 13

Update based on the comma seperated value

I have the below Table with two columns both columns are VARCHAR2(100).

PARAM_NAME              PARAM_VALUE
PlanName,DemandMonth    EUMOCP,01-2022
PlanName,DemandMonth    EUMOCP,02-2022
PlanName,DemandMonth    EUMOCP,03-2022
PlanName,DemandMonth    EUMOCP,04-2021

How can we write a update on the table so that it only updates the corresponding value.

For example:

Update DemandMonth from 01-2022 to 04-2022.

Provided it only updates the columns based on the first column For instance,

Column A  Column B
1,2        3,4

based on 1 we can update 3 as it is before ',' similarly based on 2 we can update 4.

What we want to achieve is the first it identifies where is 'DemandMonth' and then accordingly update the second column. Also if possible can we write it for 4 or 5 comma seperated values?

Upvotes: 0

Views: 51

Answers (2)

MT0
MT0

Reputation: 168291

You should not do this and should refactor your table to not use delimited strings... however, you can use:

MERGE INTO params dst
USING (
  WITH items ( rid, param_names, param_values, name, value, lvl, max_lvl ) AS (
    SELECT ROWID,
           param_name,
           param_value,
           REGEXP_SUBSTR( param_name, '[^,]+', 1, 1 ),
           REGEXP_SUBSTR( param_value, '[^,]+', 1, 1 ),
           1,
           REGEXP_COUNT( param_value, '[^,]+' )
    FROM   params
  UNION ALL
    SELECT rid,
           param_names,
           param_values,
           REGEXP_SUBSTR( param_names, '[^,]+', 1, lvl + 1 ),
           REGEXP_SUBSTR( param_values, '[^,]+', 1, lvl + 1 ),
           lvl + 1,
           max_lvl
    FROM   items
    WHERE  lvl < max_lvl
  )
  SELECT rid,
         LISTAGG(
           CASE
           WHEN name = 'DemandMonth' AND value = '01-2022'
           THEN '04-2022'
           ELSE value
           END,
           ','
         ) WITHIN GROUP ( ORDER BY lvl ) AS param_value
  FROM   items
  GROUP BY rid
  HAVING COUNT( 
           CASE
           WHEN name = 'DemandMonth' AND value = '01-2022'
           THEN 1
           END
         ) > 0
) src 
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET param_value = src.param_value;

Which, for the sample data:

CREATE TABLE params ( param_name, param_value ) AS
SELECT 'PlanName,DemandMonth',    'EUMOCP,01-2022'  FROM DUAL UNION ALL
SELECT 'PlanName,DemandMonth',    'EUMOCP,02-2022'  FROM DUAL UNION ALL
SELECT 'PlanName,DemandMonth',    'EUMOCP,03-2022'  FROM DUAL UNION ALL
SELECT 'PlanName,DemandMonth',    'EUMOCP,04-2021'  FROM DUAL;

Then:

SELECT * FROM params;

Outputs:

PARAM_NAME PARAM_VALUE
PlanName,DemandMonth EUMOCP,04-2022
PlanName,DemandMonth EUMOCP,02-2022
PlanName,DemandMonth EUMOCP,03-2022
PlanName,DemandMonth EUMOCP,04-2021

db<>fiddle here

Upvotes: 1

MT0
MT0

Reputation: 168291

Don't store values in delimited strings.

Change your table so the values are:

CREATE TABLE params ( id, param_name, param_value ) AS
SELECT 1, 'PlanName',    'EUMOCP'  FROM DUAL UNION ALL
SELECT 1, 'DemandMonth', '01-2022' FROM DUAL UNION ALL
SELECT 2, 'PlanName',    'EUMOCP'  FROM DUAL UNION ALL
SELECT 2, 'DemandMonth', '02-2022' FROM DUAL UNION ALL
SELECT 3, 'PlanName',    'EUMOCP'  FROM DUAL UNION ALL
SELECT 3, 'DemandMonth', '03-2022' FROM DUAL UNION ALL
SELECT 4, 'PlanName',    'EUMOCP'  FROM DUAL UNION ALL
SELECT 4, 'DemandMonth', '04-2021' FROM DUAL;

Then all you need to do to update the value is:

UPDATE params
SET   param_value = '04-2022'
WHERE param_name  = 'DemandMonth'
AND   param_value = '01-2022';

There is no worrying about where in the delimited string the value is and it is all simple.

Upvotes: 3

Related Questions