Issue
When I try to update the name fields, I get the first result only and in the first row only by 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 @names = SUBSTRING(@names,1, LOCATE(',', @names)-1);
UPDATE employees SET NAME = @Name
WHERE
id = @i;
SET
@i = @i + 1;
END WHILE label1;
end
Attached picture:
What I need is to add all the names sequentially.
Anyone know how to solve this problem?
Solution
Ignoring the update for now,inserting some debugging selects and using useful tstring functions..
drop procedure if exists p;
delimiter $$
create procedure p()
BEGIN
set @id1 := 2591;
set @id2 := 2594;
set @i := @id1;
SET @names = 'marwan,wael,adnan,sameh,';
select @i,@names,Locate(',', @names);
LABEL1: WHILE @names is not null and
@i < @id2 do
SET @Name = SUBSTRING_index(@names, ',',1);
select @i,@names,@name;
SET @names = replace(@names,concat(@name,','),'');
#UPDATE employees SET NAME = @Name
#WHERE
# id = @i;
SET @i = @i + 1;
END WHILE label1;
SET @Name = SUBSTRING_index(@names, ',',1);
select 'and finally' ,@i,@names,@name;
end $$
delimiter ;
call p();
+------+--------------------------+---------------------+
| @i | @names | Locate(',', @names) |
+------+--------------------------+---------------------+
| 2591 | marwan,wael,adnan,sameh, | 7 |
+------+--------------------------+---------------------+
1 row in set (0.003 sec)
+------+--------------------------+--------+
| @i | @names | @name |
+------+--------------------------+--------+
| 2591 | marwan,wael,adnan,sameh, | marwan |
+------+--------------------------+--------+
1 row in set (0.019 sec)
+------+-------------------+-------+
| @i | @names | @name |
+------+-------------------+-------+
| 2592 | wael,adnan,sameh, | wael |
+------+-------------------+-------+
1 row in set (0.023 sec)
+------+--------------+-------+
| @i | @names | @name |
+------+--------------+-------+
| 2593 | adnan,sameh, | adnan |
+------+--------------+-------+
1 row in set (0.030 sec)
+-------------+------+--------+-------+
| and finally | @i | @names | @name |
+-------------+------+--------+-------+
| and finally | 2594 | sameh, | sameh |
+-------------+------+--------+-------+
1 row in set (0.041 sec)
Query OK, 0 rows affected (0.048 sec)
Answered By – P.Salmon
Answer Checked By – Mildred Charles (BugsFixing Admin)