[SOLVED] Update different fields at same time in same column MySQL

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:

enter image description here

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)

Leave a Reply

Your email address will not be published.