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?
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 || ', %'
)
1