bstruss Posted November 4, 2005 Share Posted November 4, 2005 I accidentally added over a thousand records to a table and have deleted them. However, the auto increment for new records added at this point picks up with the highest number from the ones prior to deletion. How can I set the highest count number back to what it was before I added all these records (i.e., the highest number currently in the table)? Hope this makes sense! Thanks-- Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted November 4, 2005 Share Posted November 4, 2005 [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']alter table[/span] yourtable set auto_increment = 1; [!--sql2--][/div][!--sql3--] Quote Link to comment Share on other sites More sharing options...
bstruss Posted November 4, 2005 Author Share Posted November 4, 2005 Thanks, but I am getting a syntax error with this (substituting my table name correctly etc). Do you not have to specify the increment field in this somewhere? I will research it further. Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted November 4, 2005 Share Posted November 4, 2005 my bad, remove "set" Quote Link to comment Share on other sites More sharing options...
angel_cowgirl Posted November 5, 2005 Share Posted November 5, 2005 I did the same thing...I accidently added some rows that werent suppose to be there, deleted them, added the current data and now its misnumbered - skipping a block of numbers of the deleted ones. I tried what was suggested here but it had no effect on my table... any other ideas? I'm tryin to start my numbers at 100, but i tried the 1 like suggested here too and it didnt do anything either. no errors just nothing happens.. Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted November 5, 2005 Share Posted November 5, 2005 auto increment starts at MAX(id). so even if you reset the counter, it'll still start at the highest id existing in the table. you could either TRUNCATE the table, which would empty the table and reset the counter, or manually change the ids (just make sure there's no duplicates) and then reset the counter. if you want to start at 100, change the query i posted from 1 to 100 (make sure nothing is greater than 100, remember the MAX(id) rule). Quote Link to comment Share on other sites More sharing options...
angel_cowgirl Posted November 5, 2005 Share Posted November 5, 2005 Woohoo...got it figured out... I manually reset the numbers and tried adding another row and it still skipped numbers. But I just had misunderstood what was being told to me to fix it...it was just different ways of thinking So after reading some more about the issue I finally saw what I was doing wrong. Thanks, yet again! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.