By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE dont_store_delimited_strings (customers, products) AS
SELECT 'cust_a', 'prod_a,prod_b,prod_c' FROM DUAL UNION ALL
SELECT 'cust_b', 'prod_b,prod_d,prod_f' FROM DUAL;
2 rows affected
CREATE TABLE really_dont (products_to_remove) AS
SELECT 'prod_b' FROM DUAL UNION ALL
SELECT 'prod_c' FROM DUAL;
2 rows affected
MERGE INTO dont_store_delimited_strings dst
USING (
WITH indexed_removal (ptr, idx, max_idx) AS (
SELECT products_to_remove, ROWNUM, COUNT(*) OVER ()
FROM really_dont
),
removed (rid, products, lvl, max_lvl) AS (
SELECT s.ROWID,
REPLACE(',' || s.products || ',', ',' || i.ptr || ',', ','),
i.idx,
i.max_idx
FROM dont_store_delimited_strings s
INNER JOIN indexed_removal i
ON (i.idx = 1)
UNION ALL
SELECT r.rid,
REPLACE(r.products, ',' || i.ptr || ',', ','),
i.idx,
r.max_lvl
FROM removed r
INNER JOIN indexed_removal i
ON (r.lvl + 1 = i.idx)
)
SELECT rid,
TRIM(BOTH ',' FROM products) AS products
FROM removed
WHERE lvl = max_lvl
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
UPDATE
SET products = src.products;
2 rows affected
SELECT *
FROM dont_store_delimited_strings;
| CUSTOMERS | PRODUCTS |
|---|---|
| cust_a | prod_a |
| cust_b | prod_d,prod_f |