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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 > [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
