How to Recreate/Reset Table ID Fields (1,2,3,4…) using mySQL

Last Updated on Wednesday, 23 February 2011 10:09pm Written by spunky Wednesday, 23 February 2011 07:54am

If you are anal retentive or obsessive–compulsive about having clean ID numbers in your mySQL tables, this code should prove itself useful as it will automatically:

• clean up, convert, recount, reset, recompile, recreate, repopulate the ID column for every entry
• modify the auto increment counter for the table

It is important to note this script should not be used on tables with cross referenced identifiers meaning if a different table relies on the IDs of the modified table it will potentially break the references as a result of assigning the new IDs.

The code might also be useful for other purposes, such as cleaning up CMS page orders or providing a package with clean sample data.

Example

If your table looks like this:

ID  |  Name
-------------
1   |  Wally
4   |  Sally
6   |  Harry
12  |  Mandy

This script will modify your table to this:

ID   |  Name
-------------
1    |  Wally
2    |  Sally
3    |  Harry
4    |  Mandy

mySQL Script

-- your_table: The table to modify
-- id: The id field/column to reset

SET @num := 0;
UPDATE your_table SET id = @num := (@num+1);
ALTER TABLE your_table AUTO_INCREMENT =1;

2 Comments

1. neha   |  Wednesday, 28 September 2011 at 8:42 pm

   thankss alot…the code worked..and nw my database is in organized manner…

   
2. c0nfus3d1   |  Friday, 04 November 2011 at 8:05 am

   Thanks for this!!