Selacius Posted February 15, 2006 Share Posted February 15, 2006 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. Link to comment Share on other sites More sharing options...
aspnetguy Posted February 15, 2006 Share Posted February 15, 2006 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. Link to comment Share on other sites More sharing options...
Selacius Posted February 15, 2006 Author Share Posted February 15, 2006 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. Link to comment Share on other sites More sharing options...
aspnetguy Posted February 15, 2006 Share Posted February 15, 2006 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. Link to comment Share on other sites More sharing options...
Selacius Posted February 15, 2006 Author Share Posted February 15, 2006 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] ??? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now