MySQL Procedure nested cursor

Question

I'm struggling with MySQL procedure. So, I have two tables

Table users

id      user
--------------
 1     user_1
 2     user_2
 3     user_3
 4     user_4

Table transactions

id   user_id   transaction_type
---------------------------
 1      5           cash
 2      5         non cash
 3      5           cash
 4      5           cash
 5      5           cash

Now I want to to update user_id in transaction table and replace with id in user table sequentially.

The result I want is like this

 id   user_id   transaction_type
---------------------------
 1      1           cash
 2      2         non cash
 3      3           cash
 4      4           cash
 5      1           cash

I've tried to make procedure like below but not work proper and not update sequentially.

CREATE DEFINER=`root`@`%` PROCEDURE `sequentially_update`()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE i int DEFAULT 500;
    DECLARE userId int DEFAULT 0;
    DECLARE transactionType int DEFAULT 0;
    
    DEClARE curTransactions  CURSOR FOR 
        SELECT * FROM transactions;
        
    DECLARE curUsers CURSOR FOR
        SELECT user_id FROM users
        ORDER BY RAND() LIMIT 1;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    
    OPEN curTransactions; 
        loopTransactions: LOOP 
            FETCH curContacts INTO transactionType; 
            IF finished = 1 THEN 
                LEAVE loopTransactions; 
            END IF; 
            OPEN curUsers; 
                FETCH curUsers INTO userId; 
            CLOSE curUsers; 
            UPDATE transactions SET user_id = userId;
        END LOOP loopTransactions;
    CLOSE curTransactions;
END 

How to create correct procedure in MySQL with this case ?

Answer 1

I suggest a slightly different approach which avoids the use of 2 handlers (1 for user 1 for transactions) and use an offset,limit for users. NB you only need id from transactions

 DROP TABLE IF EXISTS T,T1;
 
 CREATE TABLE T
 (id  INT,    user VARCHAR(10));
 INSERT INTO T VALUES
(1   ,  'user_1'),
(2   ,  'user_2'),
(3   ,  'user_3'),
(4   ,  'user_4');

CREATE TABLE T1
(id INT,  user_id INT,  transaction_type VARCHAR(10));
INSERT INTO T1 VALUES
( 1   ,   5   ,        'cash'),
( 2   ,   5   ,        'non cash'),
( 3   ,   5   ,        'cash'),
( 4   ,   5   ,        'cash'),
( 5   ,   5   ,        'cash');

DROP PROCEDURE IF EXISTS P;
DELIMITER $$
CREATE PROCEDURE P()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE i int DEFAULT 500;
    DECLARE userId int DEFAULT 0;
    DECLARE VtransactionID int DEFAULT 0;
    declare voffset int default 0;
    declare vid int;
    declare vmax_rows int;
    
    DEClARE curTransactions  CURSOR FOR 
        SELECT ID FROM t1 order by id;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    
    select count(*) into vmax_rows from t;
    select vmax_rows;
    OPEN curTransactions; 
        loopTransactions: LOOP 
            FETCH curTransactions  INTO Vtransactionid; 
            IF finished = 1 THEN 
                LEAVE loopTransactions; 
            END IF; 
            select voffset;
            select id into vid from t order by id limit voffset,1;
            set voffset = voffset + 1;
            if voffset  = vmax_rows then 
                set voffset = 0 ;
            end if;
            UPDATE t1 SET user_id = vid WHERE ID = vtransactionID;
        END LOOP loopTransactions;
    CLOSE curTransactions;
END  $$
delimiter ;

call p();

select * from t1;

+------+---------+------------------+
| id   | user_id | transaction_type |
+------+---------+------------------+
|    1 |       1 | cash             |
|    2 |       2 | non cash         |
|    3 |       3 | cash             |
|    4 |       4 | cash             |
|    5 |       1 | cash             |
+------+---------+------------------+
5 rows in set (0.00 sec)