how to repopulate/recreate autoincrement in mysql

Question

I want to know if I can repopulate the autoincrement value in mysql.

Because, I have records that look similar:

ID Name
1  POP
3  OLO
12 lku

Basically , I want a way to update the ID to this

ID Name
1  POP
2  OLO
3  lku

Is there any way to do this in mysql?

Thanks.

Answer 1

It's not best practice to fiddle your primary keys - better to let your DB handle it itself. There can be issues if, in between the UPDATE and ALTER, another record is added. Because of this, you must LOCK the table, which might hang other queries and spike load on a busy production server.

LOCK TABLES table WRITE
UPDATE table SET id=3 WHERE id=12;
ALTER TABLE table AUTO_INCREMENT=4;
UNLOCK TABLES 

OR - for thousands of rows (with no foriegn key dependencies):

CREATE TEMPORARY TABLE nameTemp( name varchar(128) not null )
INSERT INTO name SELECT name FROM firstTable
TRUNCATE firstTable
INSERT INTO firstTable SELECT name FROM nameTemp

The latter method will only work where you have no foreign keys. If you do, you'll require a lookup table.

CREATE TEMPORARY TABLE lookup( newId INTEGER AUTO_INCREMENT, oldId INTEGER, PRIMARY KEY newId( newId ) );
INSERT INTO lookup (oldId) SELECT id FROM firstTable
[do temp table queries above]

You now have a lookup table with the old to new ids which you can use to update the foreign key dependencies on your other tables (on a test server!)