MySQL Stored Procedure Looping times out

Question

I have a table called maptable that looks like this:

occ_id     name               position
0124       Mark Jones         2
           Hanna Smith        3 
           Frank Williams     4
0238       Henry Bassett      5

I am trying to 'fill in' the occ_id column where missing (meaning, Hanna Smith and Frank Williams should also have occ_id 0124).

I came up with the following procedure which does the job but with ~6000 records, it times out whenever I run it:

CREATE DEFINER=`root`@`localhost` PROCEDURE `mapjoinid_1`()
MODIFIES SQL DATA
BEGIN
        DECLARE n INT DEFAULT 0;
        DECLARE i INT DEFAULT 0;
        SELECT COUNT(*) FROM maptable WHERE occ_id = '' INTO n;
        SET i=0;
                WHILE i < n DO
                UPDATE maptable m JOIN maptable mt ON (m.position - 1) = mt.position SET m.occ_id = mt.occ_id;
                SET i = i + 1;
                END WHILE;
        End

Is there a more efficient way to LOOP or REPEAT the update statement where it doesn't take so long to process?

Answer 1

The efficiency issue most likely comes from the declaration of the Join within a while loop. MySQL is not particularly good at running these sorts of functions, so one way around it would be to do a nested select in your MySQL update: MySQL - UPDATE query based on SELECT Query. If you are not planning on running this procedure repeatedly (as one might with a stored procedure), then I think this is the most efficient course of action.

The time out might also come from your variable declaration: double check to see all your variables are incremented and storing correctly.