Update multiple rows in MySQL between two Id-CodePudding

I'm try to update different rows at one time through a group of names I use the following function:

BEGIN

set @id1 := 2591;
set @id2 := 2594;

set @i := @id1;

SET
  @names = 'marwan,wael,adnan,sameh,';

LABEL1: WHILE (Locate(',', @names) > 0) do
SET
  @Name = SUBSTRING(@names, 1, LOCATE(',', @names));

SET @Name = SUBSTRING(@names,1, LOCATE(',', @names)-1);
UPDATE employees SET NAME = @Name
WHERE
  id = @i;

SET
  @i = @i   1;

END WHILE label1;
end

The function works, but the problem is that it only adds the first name and it's repeated in all rows. attached picture:

Anyone know how to solve this problem?

CodePudding user response：

Your iterational cycle checks the next condition:

WHILE (Locate(',', @names) > 0)

But @names variable is not altered within the cycle - hence you have infinity loop.

CodePudding user response：

SUBSTRING(@names, 1, LOCATE(',', @names)-1)

The problem is here. Both of your calls to SUBSTRING start with the first character so they always find the first name in the list. You should move the start index further for subsequent calls.

And also the function does not stop because the value of @names never changes. No matter how many times it runs, the following check always returns a value greater than 0.

 WHILE (Locate(',', @names) > 0) do

You should trim processed names from the variable.