Query to Re-index Primary Key of MySQL Database

LearningDeveloper picture LearningDeveloper · Apr 20, 2012 · Viewed 17.1k times · Source

I've got a table in MySQL that has a Primary Key Column.

Lets say:

ID | Value
1  | One
2  | Two
6  | Three
8  | Four
9  | Five

How do I get it to be:

ID | Value
1  | One
2  | Two
3  | Three
4  | Four
5  | Five

There are no other tables. Just the one. I just want the ID to be in a proper series.

Any suggestion?? A Query perhaps.. :)

Answer

Yogesh Suthar picture Yogesh Suthar · May 24, 2015

There is even a simple way to accomplish the result by writing this query

SET @newid=0;
UPDATE tablename SET primary_key_id=(@newid:=@newid+1) ORDER BY primary_key_id;

This query will reindex the primary key starts from 1