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?

Cheers

Answers


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);

Need Your Help

Jquery Javascript only works on chrome and firefox, but not on IE

javascript jquery google-chrome internet-explorer firefox

I have a HTML file which contains CSS and Jquery. When I tested it on chrome and Firefox, it works fine. But when I tested it on IE, only the CSS part work, the click function in JavaScript doesn't...

UIView Animation with Simultaneous Counting Thread

ios objective-c multithreading animation uiview

I am trying to get a UIView to expand using an animation block, which works perfectly. However, I want a UILabel to start at 0 and every 0.01 seconds to add 1 until it gets to 100.