How to Handle SQL Update with Indefinite Number of Exclusion Columns?

Question

I'm working on updating a large SQL database and need to manage dynamic exclusions specified in a comma-separated list within the exclude_fkidval column. Below is the structure of the table before applying the SQL update.

Initial table structure:

id	fkidval	bal	exclude_fkidval
r1	fv10		fv100
r2	fv11		fv111
r3	fv12		fv123
r4	fv13		fv134

Current query:

UPDATE tab
SET bal = (SELECT credit - debit
           FROM f_tab
           WHERE f_tab.fkval LIKE fkidval || '%' 
             AND f_tab.fkval <> exclude_fkidval)

Given that the exclusions in exclude_fkidval might expand indefinitely, I need to adapt the SQL update to handle dynamically varying numbers of exclusions as illustrated below:

id	fkidval	bal	exclude_fkidval
r1	fv10		fv100, fv101, fv102
r2	fv11		fv111
r3	fv12		fv123, fv120
r4	fv13		fv134

Question

How can I efficiently adapt this SQL update query to manage multiple exclusions from the comma-separated exclude_fkidval in each row, considering this impacts a large dataset?

Answer 1

Good practice would be to not use delimited lists and, instead, use a separate table to store the exclusions.

However (if you are stuck with your current implementation), you can compare sub-strings in the list (including the surrounding delimiters):

UPDATE tab
SET bal = (
    SELECT credit - debit
    FROM   f_tab
    WHERE  f_tab.fkval LIKE fkidval || '%'
    AND    ', ' || exclude_fkidval || ', ' NOT LIKE '%, ' || f_tab.fkval || ', %'
)