MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Employee (id INT)
SELECT 1 id UNION
SELECT 31 UNION
SELECT 35 UNION
SELECT 100;
SELECT * FROM Employee;


id


1

31

35

100


ALTER TABLE Employee ADD COLUMN new_column INT;
UPDATE Employee t0
  JOIN ( SELECT t1.id, COUNT(t2.id) cnt
         FROM Employee t1
         JOIN Employee t2 ON t1.id >= t2.id
         GROUP BY t1.id ) t3 ON t0.id = t3.id
SET t0.new_column = t3.cnt;
SELECT * FROM Employee;


id
new_column


1
1

31
2

35
3

100
4