0

I have one MYSQL table.

In that table there is one column name SRNO is its primary key and auto increment also.

I have around 25000 records in that table but some records between the table is deleted.

For example record no 10 is deleted etc.

I want a SQL script to run mysql to regenerate all auto increment field so that it start from 1 again and end at last record to avoid that missing records.

Thanks

Jeffrey Blake
  • 9,399
  • 6
  • 41
  • 64
air
  • 5,906
  • 26
  • 88
  • 124

1 Answers1

0

This is generally not considered a good idea, as you can seriously mess with referential integrity this way. If this is an aesthetics thing, I'd recommend leaving it as-is. That being said, here's what you do:

  • create a new table with the same schema as the old one, and set your auto-increment to 1.
  • insert all records from the old table into the new one, except for the auto-increment column.
  • rename the original table to something else
  • rename the new table to the original name

this will effectively "reseed" your table.

Jeremy Holovacs
  • 21,245
  • 30
  • 107
  • 245