Maarethyu Maarethyu - 6 months ago 8
MySQL Question

Update rows where in csv field with MySQL

I have a column in my MySQL table that is filled with comma separated ids.
I wanna update all the rows that have their itemID in the csv list:

UPDATE items SET ... WHERE itemID IN(SELECT <csv items id> FROM list WHERE ...)


Unfortunately, this only affect the first itemID in the csv list.

I also tried this:

SELECT @csv := <csv items id> FROM list WHERE ...;
UPDATE items SET ... WHERE itemID IN(@csv);


And it's the same thing, only the first value in the csv list get updated.

Here are some pics to illustrate:


Answer

I suggest the use of FIND_IN_SET (if the result is != then teh ItemID match a value in comma_delimited_string

 UPDATE items 
  SET  your_column = your_result
 WHERE FIND_IN_SET(ItemID, your_comma_delimited_string) != 0;