Yes, for a MyIsam type table. Ed
-----Original Message----- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 29, 2007 11:53 AM To: emierzwa Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Reset a auto increment field? 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, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> 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: [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/mysql? > [EMAIL PROTECTED] > > -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
