Jump to content

Archived

This topic is now archived and is closed to further replies.

bstruss

Resetting highest count after delete

Recommended Posts

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--

Share this post


Link to post
Share on other sites

[!--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--]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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! :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.