What MySQL statement needed to populate a field with incremental number
I have an old MS Access DB which I'm translating to a MySQL DB. I used bullzip to create the database but due to bad design the old MS Access database didn't have a unique primary key for most of the tables.
So I've created a id field but obviously it's empty for each entry, I wonder if there's a simple statement I can use to fill them up with 1, 2, 3, 4 etc...
EDIT: I think I haven't gotten my question across properly. I know all about auto increment. Thats not the problem.
I have a table, full of records which I need kept and which came from a Access database that didn't have a unique id defined as a field. In otherwords I have fields for firstname, surname etc etc but no field 'id'. This seems absolutely crazy but apparently this database has been well used and never had any unique ids for any tables bar one. Weird!
Anyway, I've created a field in the table for id (and set it to auto increment of course) but obviously all the existing records don't have an id set currently. So I need to create one for each record.
Is there a way to fill all these records with unique numbers using a mysql statement?
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.
mysql> ALTER TABLE TheTable ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY; mysql> SELECT id FROM TheTable; -- outputs values 1, 2, 3, etc.
If you made an id column but didn't declare it AUTO_INCREMENT PRIMARY KEY, you can populate the column like this:
mysql> SET @id := 0; mysql> UPDATE TheTable SET id = (@id := @id+1);