MySQL 循环更新

Question

SQL 专家们，我对如何完成这项任务感到困惑。我有一个包含 40k 条记录的 MySQL 数据库表，我需要使用标识符(循环方式)更新 group 列。标识符是预定义的(2、5、9)。

我如何相应地更新此表？应该类似于下面的示例:

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

非常感谢任何帮助!

Answer 1

在研究了数十篇文章之后，我制定了一个两步方法来实现我所需要的。对于其他可能遇到此问题的人来说，我就是这样做的:

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;