Query to Re-index Primary Key of MySQL Database

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.. 🙂

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

SET @newid=0;
UPDATE tablename SET primary_key_id=(@newid:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6d502d03081a0409">[email protected]</a>+1) ORDER BY primary_key_id;

This query will reindex the primary key starts from 1

Method 2

Seems to me you have two options.

1) create a new table and copy the existing data over.

2) add another autoincrement field to the existing table, then delete the original column.

ALTER TABLE tableName ADD NewIdn INT NOT NULL AUTO_INCREMENT KEY

Method 3

I did this in phpmyadmin by unchecking the A_I box (Auto Increment setting), clicking save, and then checking it again, and clicking save again.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating