In Mysql, how to fill a column with incremental integers?-CodePudding

I have a database named "planning" in which a column named "planning_id" is badly formatted, indeed it's only filled with 0s.

I would like to fill it incrementally with proper IDs; like 1, 2, 3, 4 etc.

I have think about a sql formula like this one:

BEGIN
DECLARE i INT DEFAULT 1; 
WHILE (i <= 68) DO
    UPDATE planning set planning_id = i;
    SET i = i 1;
END WHILE;
END;

But it creates an error (I translate from french to english):

Unrecognized keywords. Near DECLARE.

Any idea ?

CodePudding user response：

If you don't mind the sorting of your columns you can DROP the planning_id and add it again now as Primary Key and with auto_increment like :

ALTER TABLE planning DROP COLUMN planning_id, 
ADD COLUMN planning_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, 
ADD PRIMARY KEY (planning_id);

This way your planning_id will refresh their values in an incremental order (1, 2, 3, 4 and so on).

Check MySQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted

CodePudding user response：

ALTER TABLE table_name AUTO_INCREMENT = start_value;

use auto increment which is the attribute to use when you want MySQL to assign a sequence of numbers automatically to a field (in essence, creating an autonumber field).