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.
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!)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With