Maarethyu Maarethyu - 1 year ago 45
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 Source

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;