I deleted 11 rows and I want the auto increment id values to reset to 6,7,8,9 … How can I achieve that without using the update statement each row one at a time. I want to find out if there is a way to avoid this sort of issue in the beginning when creating the database.

enter image description here


You cannot prevent this issue of reordering once you begin inserting data.

Notwithstanding, if you want to reorder the IDs sequentially, you have no choice but to rebuild the table.

Here is the SQL to do that

ALTER TABLE students RENAME students_outoforder;
CREATE TABLE students LIKE students_outoforder;
INSERT INTO students (stud_name,class,subject)
SELECT stud_name,class,subject FROM students_outoforder ORDER BY id;

After running this, run this

SELECT * FROM students;

You will see the IDs reordered 1..8 and the AUTO_INCREMENT of the table at 9

Then, you can get rid of the old table

DROP TABLE students_outoforder;

Give it a Try !!!

CAVEAT: Do not try this with the students table has foreign key constraints in it.

| improve this answer | |
  • To detail more why it cannot be done, is because AUTO_INCREMENT has to be always > max(auto_increment_column). And that is ok because in production databases you normally do not want to change ids. – jynus Aug 3 '14 at 8:12
  • This is an alternative option without reimporting and DROPing: set @id := 0; update students set id = @id:=@id +1 ORDER BY id; ALTER TABLE students AUTO_INCREMENT = #number_here_as_variables_aint_allowed; Works even better in 5.6. – jynus Aug 3 '14 at 8:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.