Jump to content


Photo

Resetting highest count after delete


  • Please log in to reply
6 replies to this topic

#1 bstruss

bstruss
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 04 November 2005 - 03:55 PM

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

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 04 November 2005 - 04:23 PM

[!--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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 bstruss

bstruss
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 04 November 2005 - 05:26 PM

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.

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 04 November 2005 - 05:29 PM

my bad, remove "set"
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 angel_cowgirl

angel_cowgirl
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationPoedunk America

Posted 05 November 2005 - 12:31 PM

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

#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 05 November 2005 - 05:08 PM

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).
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 angel_cowgirl

angel_cowgirl
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationPoedunk America

Posted 05 November 2005 - 10:46 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users