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!

Check here if any answer works for you.https://stackoverflow.com/questions/35903375/how-to-update-large-table-with-millions-of-rows-in-sql-server — ALourenço, Oct 25 '17 at 15:58
@ALourenço Thanks, but doesn't work for me. Those answers all revolve around mass assignment of the same value while I want to round robin three different values. — NightOwlPrgmr, Oct 25 '17 at 16:09

NightOwlPrgmr · Accepted Answer · 2017-10-26T16:48:06.817

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;

MySQL Round Robin Update

1 Answers1