mysql auto_increment 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.
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;
TRUNCATE TABLE students;
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;
SHOW CREATE TABLE students\G


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.