Update Columns With Array (Mysql Procedure)

Question

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!

Why are you doing this entirely within MySQL ? It just seems unlikely that someone would manually pass an array to a database. — Strawberry
– Strawberry, Commented Feb 13, 2016 at 9:59
Because i am sending some text_ids to convert their read_by values +1 in DB. I can do it with php loop but i thought send all these ids in one array and done rest operations with mysql procedure will give me a better performance. — Başar Söker
– Başar Söker, Commented Feb 13, 2016 at 10:04
I see. I suspect the PHP solution will be faster. Remember to build the query inside the loop, but execute the query outside the loop. — Strawberry
– Strawberry, Commented Feb 13, 2016 at 10:39

setsab · Accepted Answer · 2016-02-13 09:11:27Z

2

If you are interested in having a non-array approach, FIND_IN_SET might help.

This might help you get started:

SET @strIDs ="1011,1012,1013"
UPDATE chat_texts a SET a.read_by=a.read_by+1 WHERE FIND_IN_SET(a.text_id, @strIDs);

answered Feb 13, 2016 at 9:11

setsab

3393 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Başar Söker Over a year ago

doesn't work. still getting same issue. i think main problem is passing array to procedure because when i use UPDATE chat_texts a SET a.read_by=a.read_by+1 WHERE a.text_id IN ('1010','1011') it works as expected but when i call procedure it is taking param like ('1010,1011')

Başar Söker · Accepted Answer · 2016-02-13 09:51:06Z

1

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

answered Feb 13, 2016 at 9:51

Başar Söker

72710 silver badges18 bronze badges

Collectives™ on Stack Overflow

Update Columns With Array (Mysql Procedure)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related