Resetting Primary Column With auto_increment

[Selacius]

I have a table, that has a column which is set to primary and also auto_increment. I am curious if there is a simple way to reset the counter back to 0. Two examples of this are:a) The table is full of 15 rows, with IDs 1-15. I delete row with ID=5. Now I want the table to be reset so that now the IDs are 1-14. I have 5 rows with IDs 1, 4, 7, 10, 15. Now I want them to be reset so that they will have IDs from 1-5. Without using a for statement to actually physically modify and update the rows, is there a way to reset the counter back to 0? Another example of this is:c) The table has 5 rows, the IDs are not important, although the final ID is 15, which means the next row will be ID 16. I empty out this table and reinsert data. I want the new rows to start back at 1, instead of 16.

[aspnetguy]

I am affraid there isn't. YOu would have ot manually loop through and do it.Can I ask why you need to do this??? If the id are referenced on another page then that page will break....especially if it is referencing id 15 which would no longer exist.

[Selacius]

Its for an automatic fight system i am creating. When a player dies, he is eliminated from the database, and the ids have to be reset.

[aspnetguy]

I recommend you read my blog post about sql arrays. You are going to need to use a similar approach to manually loop through the ids.This is for SQL Server but there should be very little diffences converting ot MySql.http://www.aspnetguy.com/Entry.aspx?e=18Note: In SQL Server if a column is set to auto_increment then it disallows you to edit that column...I don't know if MySql is similar...if it is then you may be out of luck.You may have ot trun auto_increment off anf increment manually when inserting a record.

[Selacius]

Alrightie. Well I can change the IDs using a for loop, so it won't be too bad. And I think I'll just code in that when the script is finished running it will delete the table and remake it, so the ID will start back at 0.EDIT:Would this work by anychance? I am thinking more for when I completely empty the table and want the IDs to start back at 0.ALTER TABLE [tablename] AUTO_INCREMENT = [number] ???