CODEWITHSUNDEEP
mysqlprocedure
pvgdev
pvgdev

Reputation: 157

MySQL Stored Procedure Loop Increment Value, Only Getting Final Value

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 |
+----------+

Upvotes: 1

Views: 852

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

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)

Upvotes: 1

Related Questions