CODEWITHSUNDEEP
mysqlcsvselect
Maarethyu
Maarethyu

Reputation: 83

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:

Upvotes: 1

Views: 82

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

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;

Upvotes: 1

Related Questions