Search code examples
mysqlarrayssql-updateprocedurewhere-in

Update Columns With Array (Mysql Procedure)

I have array like this

text_id_array=["1011", "1012", "1013"]

I want to pass it to mysql procedure for update columns where it matches with array members so i tried 

BEGIN
UPDATE chat_texts a SET a.read_by=a.read_by+1 WHERE a.text_id IN (text_id_array); 
END

but it only works on first member of array 1011. I know there is a lot of question like that but i couldn't find the solution. Thanks for helps!

Solution

  • Now i found it. Thanks for recommend to use FIND_IN_SET but also i have noticed that i should change my param to VARCHAR. So there it is

    CREATE DEFINER=`root`@`localhost` PROCEDURE `aa`(IN `text_array_id` VARCHAR(255))
BEGIN
UPDATE chat_texts a SET a.read_by=a.read_by+1 WHERE FIND_IN_SET(a.text_id, text_array_id);
END