resetting a auto_increment column

Hello,

I would like to reset a column using auto_increment to a certain value.

eg i have 5 entries but because i have deleted 3 auto_increment thinks the next entry will be number 9.

How can i set the 'counter' back to 6?

Thanks,
Shane

AFAIK, there is no way... because the auto_increment fields are meant to be unique.

damn. i can see why they would have done it that way though... 🙁

Why not use:

alter table <your table> auto_increment=<next number>

So long as the <next number> doesn't exist you're all set. I use it all the time, and it works just fine.

Thanks for the info T&A!!!

That's exactly what I've been looking for. :o)

well what if you delete one in the middle?

table: cool_kids

id name

1 keith
2 stupid-guy
3 flo

mysql> delete from cool_kids where id = 2;
becomes...

table: cool_kids

1 keith
3 flo

what can i do? recalculate id numbers? is it smart enough to stick someone in there?

keith