MySQL Stored Procedure Loop Increment Value, Only Getting Final Value

Question

I am writing a very simple program to update the location INT column in the 'todo' table for existing rows. The objective is to set the location value incrementally for each user, starting at 0. When running the code, it seems to only capture the final value for each user's loop. Is there something I'm misunderstanding about MySQL's variables? Or maybe I messed up the loops..

This has been a late night >.<

BEGIN
    DECLARE loop_done INT DEFAULT 0;
    DECLARE current_user_id INT;
    DECLARE current_todo_id INT;
    DECLARE todo_position INT DEFAULT 0;
    DECLARE cur_users CURSOR FOR 
        SELECT id FROM users;
    DECLARE cur_todos CURSOR FOR 
        SELECT id FROM todo WHERE user_id = @uid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_done = 1;

    OPEN cur_users;
    loop_users: LOOP 

        FETCH cur_users INTO current_user_id;

        IF loop_done = 1 THEN
            SET loop_done = 0;
            LEAVE loop_users;
        END IF;

        SET @uid = current_user_id;
        SET todo_position = 0;

        OPEN cur_todos;

        loop_todos: LOOP 
            FETCH cur_todos INTO current_todo_id;
            IF loop_done = 1 THEN
                SET loop_done = 0;
                SET todo_position = 0; 
                LEAVE loop_todos;
            END IF;

            UPDATE todo SET position = todo_position WHERE user_id = @uid;
            SET todo_position = todo_position + 1;

        END LOOP loop_todos;

        CLOSE cur_todos;

    END LOOP loop_users;
END$$

Here are the actual results:

+----------+
| position |
+----------+
|        3 |
|        3 |
|        3 |
|        3 |
|        4 |
|        4 |
|        4 |
|        4 |
|        4 |
+----------+

These are the expected results:

+----------+
| position |
+----------+
|        0 |
|        1 |
|        2 |
|        3 |
|        0 |
|        1 |
|        2 |
|        3 |
|        4 |
+----------+

Change: UPDATE `todo` SET `position` = `todo_position` WHERE `user_id` = @`uid`;. by UPDATE `todo` SET `position` = `todo_position` WHERE `id` = `current_todo_id`; — wchiquito
– wchiquito, Commented Nov 20, 2017 at 11:46

P.Salmon · Accepted Answer · 2017-11-20 11:55:48Z

1

I don't think you need cursors at all for example

drop table if exists todo;

create table todo (id int, user_id int, position int);

truncate table todo;
insert into todo values (1,1,null),(2,1,null),(2,2,null);


update todo t join  (
            select id,user_id, 
            if(user_id <> @p,@rn:=0,@rn:=@rn+1) rn ,
            @p:=user_id p
            from todo, (select @rn:=0,@p:=0) r
            order by user_id,id
            ) s on s.id = t.id and s.user_id = t.user_id
    set position = s.rn

    where 1  = 1;

Simulates row number functions found in other dbs using a variable and returns

select * from todo;

+------+---------+----------+
| id   | user_id | position |
+------+---------+----------+
|    1 |       1 |        0 |
|    2 |       1 |        1 |
|    2 |       2 |        0 |
+------+---------+----------+
3 rows in set (0.02 sec)

answered Nov 20, 2017 at 11:55

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

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

1 Comment

pvgdev Over a year ago

Worked like a charm, and much more expressive. Thanks.

Collectives™ on Stack Overflow

MySQL Stored Procedure Loop Increment Value, Only Getting Final Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related