If I understand you correctly, if my table is MyISAM, after I did a
delete query I could just: "ALTER TABLE t2 AUTO_INCREMENT="1"; and
that would cause the auto increment value to be set to 901 (Assuming
900 total current records) on the next insert?


On Aug 29, 2007, at 1:48 PM, <emierzwa@micron.com>
<emierzwa@micron.com> wrote:

> To change the value of the AUTO_INCREMENT counter to be used for new
> rows, do this:
>
> ALTER TABLE t2 AUTO_INCREMENT = value;
>
> You cannot reset the counter to a value less than or equal to any that
> have already been used. For MyISAM, if the value is less than or equal
> to the maximum value currently in the AUTO_INCREMENT column, the value
> is reset to the current maximum plus one. For InnoDB, you can use
> ALTER
> TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the
> value is
> less than the current maximum value in the column, no error message is
> given and the current sequence value is not changed.
>
>
> Ed
>
> -----Original Message-----
> From: dpgirago@mdanderson.org [mailto:dpgirago@mdanderson.org]
> Sent: Wednesday, August 29, 2007 11:02 AM
> To: Jason Pruim
> Cc: MySQL List
> Subject: Re: Reset a auto increment field?
>
>> Is there away to reset an auto incrementing field count? I have a
>> database that currently has 935 records in it but because I have
>> deleted a few the current number used for NEW records is 938 How
>> can I get it to count the records and assign a record number based on
>> the total count?
>>
>> Hope that makes sense! Thanks for looking!
>>
>> --
>
>> Jason Pruim
>> Raoset Inc.
>> Technology Manager
>> MQC Specialist
>> 3251 132nd ave
>> Holland, MI, 49424
>> www.raoset.com
>> japruim@raoset.com
>
> AFAIK, you need to drop and then recreate the auto-increment field,
> otherwise you'll get holes when you delete a record.
>
> David
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=japruim@raoset.com
>
>

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com