Rectangle 27 0

sql What MySQL statement needed to populate a field with incremental number?


mysql> ALTER TABLE TheTable ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
mysql> SELECT id FROM TheTable;
-- outputs values 1, 2, 3, etc.
mysql> SET @id := 0;
mysql> UPDATE TheTable SET id = (@id := @id+1);

If you add an new id column to an existing table and make it AUTO_INCREMENT PRIMARY KEY, it will automatically populate it with incrementing values.

If you made an id column but didn't declare it AUTO_INCREMENT PRIMARY KEY, you can populate the column like this:

You are awesome! Thanks for the help! Works a treat

Note
Rectangle 27 0

sql What MySQL statement needed to populate a field with incremental number?


Use a predefined AUTO_INCREMENT field, and set the value as NULL when inserting new records, so that it automatically builds up an appropriate incrementer. Aside from that, there is no way (unless using a procedure) to create an incrementing set of values

Note