我想在mysql中进行更新查询的循环。但我还是没有找到最好的方法来进行查询。
我想提出的问题与此类似。

SET  @num := 0;
UPDATE my_table a SET a.numbering = @num := (@num+1) where a.year = 2020 and a.id_unit = 1;
SET  @num := 0;
UPDATE my_table a SET a.numbering = @num := (@num+1) where a.year = 2020 and a.id_unit = 2;
SET  @num := 0;
UPDATE my_table a SET a.numbering = @num := (@num+1) where a.year = 2020 and a.id_unit = 3;
SET  @num := 0;
UPDATE my_table a SET a.numbering = @num := (@num+1) where a.year = 2020 and a.id_unit = 4;
SET  @num := 0;
UPDATE my_table a SET a.numbering = @num := (@num+1) where a.year = 2020 and a.id_unit = 5;

但如果可能的话，我想用loop来实现，就像这样。

SET @x = 0;

SET @num := 0;
WHILE
    ( @x < 40 ) DO
    UPDATE my_table a 
    SET a.numbering = @num := ( @num + 1 ) 
WHERE
    a.year = 2020 
    AND a.id_unit = @x := ( @x + 1 );

END WHILE;