-1

I have table having serial no. 1,2,3,4,5 and so on If I delete the data of for ex. 2 no. then my table shows 1, 3,4,5,6.... I want it should reset the serial no field. Can anybody help me in this?

Pravin

pravinkumar khairnar
  • 21
  • 4
  • 2
    That would mess up the whole Application if you are using the ID somewhere else. Its not recommended. – Parthapratim Neog Mar 17 '16 at 10:23
  • 4
    Possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) – Vicky Thakor Mar 17 '16 at 10:24
  • I don't agree that this is a duplicate of the question @VickyThakor mentioned. This one is about renumbering existing rows. – O. Jones Mar 17 '16 at 10:44

1 Answers1

0

You can renumber the rows of your table by recreating it. That's probably the most foolproof way to do it if the table is small ... 100K rows or smaller.

Something like this would do the trick.

 RENAME mytable TO mytable_previous;
 CREATE mytable LIKE mytable_previous;
 TRUNCATE TABLE mytable;
 ALTER TABLE mytable AUTO_INCREMENT = 1;

 INSERT INTO mytable 
        (col, col, col) /* name all cols except autoincrementing */
 SELECT (col, col, col) /* same names, same order */
  ORDER BY id;

This will allow the INSERT operation to redo the autoincrementing in the same order as the previous table, but without gaps.

BEWARE ... This is probably a bad idea. If the autoincrementing id column is used in other tables as a reference to this table, doing this will wreck your database. Production database tables that have DELETE operations ordinarily just live with gaps in the autoincremented row numbers. Those gaps certainly cause no harm.

O. Jones
  • 103,626
  • 17
  • 118
  • 172