1

I have a value (IP Address) in TableA. If this value does not exist in the comma separated value field in TableB I need to delete the row in TableA. I can retrieve these IP's with the following but I have to repeat for each entry in the CSV String. The number of values in TableB's CSV String column can fluctuate. My Stringsplit function is a simple Parsing (Substr()) of the string the comma and position are the parameters. The last parameter needs to increase each pass until done 

*SELECT * FROM TableA AS A
JOIN `TableB` AS B ON B.ID = A.ID
WHERE 
A.`column` <> stringsplit(B.`Column`,',',1) AND
A.`column` <> stringsplit(B.`Column``,',',2) AND
A.`column` <> stringsplit(B.`Column`,',',3) AND
 ETC......

I can obtain an accurate count of these values by:

SELECT CASE WHEN TableB.Column = '' THEN 0 ELSE (LENGTH(TableB.Column`) - LENGTH(REPLACE(TableB Column, ',', ''))+1) END AS value_count
  FROM `TableB`*

How can this be done dynamically adjusting the parameter in the Stringsplit() to increase with each pass?

GMB
  • 216,147
  • 25
  • 84
  • 135
LJLadyTech
  • 11
  • 2
  • Please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and you can find here on SO some thread how to make comma separated Fileds in to rows. – nbk Feb 21 '20 at 22:45
  • Having a column containing a comma-separated list of values is almost always a sign of a poor database design. It often leads to the kinds of problems you are having here. – Honeyboy Wilson Feb 22 '20 at 02:49

1 Answers1

0

I have a value (IP Address) in TableA. If this value does not exist in the comma separated value field in TableB I need to delete the row in TableA

You could use not exists with a correlate subquery that leverages find_in_set() to search for the values from tablea in the CSV lists stored in tableb :

delete from tablea
where not exists (
    select 1 from tableb where find_in_set(tablea.column, tableb.column)
)
GMB
  • 216,147
  • 25
  • 84
  • 135