MySQL Round Robin Update

Question

SQL gurus, I'm stumped on how I'm going to complete this task. I have a MySQL database table with 40k records that I need to update the group column with an identifier (round robin style). The identifiers are predefined (2, 5, 9).

How could I update this table accordingly? Should look something like the example below:

record     group
-----------------
record A   2
record B   5
record C   9
record D   2
record E   5
record F   9
record G   2

Any help is greatly appreciated!

Answer 1

Well after researching dozens of articles I formulated a two-step approach to achieve what I needed. For others who may come across this here is what I did:

Step 1: created a stored procedure to loop through and assign a number to each record. The numbers where 1-3 to represent the three round robin values I had (2, 5, 9). Below is the procedure:

DROP PROCEDURE IF EXISTS ezloop;
DELIMITER ;;

CREATE PROCEDURE ezloop()
BEGIN
DECLARE n, i, z INT DEFAULT 0;
SELECT COUNT(*) FROM `table` INTO n;
SET i = 1;
SET z = 1;
WHILE i < n DO 
  UPDATE `table` SET `group` = z WHERE `id` = i;
  SET i = i + 1;
  SET z = z + 1;
  IF z > 3 THEN
      SET z = 1;
  END IF;
END WHILE;
End;
;;

DELIMITER ;
CALL ezloop();

Step 2: created a simple UPDATE statement to update each of the values to my actual round robin values and ran it once for each group:

UPDATE `table` SET `group` = 9 WHERE `group` = 3;
UPDATE `table` SET `group` = 5 WHERE `group` = 2;
UPDATE `table` SET `group` = 2 WHERE `group` = 1;