From: <emierzwa
Date: August 29 2007 5:48pm
Subject: RE: Reset a auto increment field?
List-Archive: http://lists.mysql.com/mysql/208795
Message-Id: <5E917D41FA2A4F4083C2CA01E940A22F1A0130@ntxboimbx34.micron.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

 To change the value of the AUTO_INCREMENT  counter to be used for new
rows, do this:

ALTER TABLE t2 AUTO_INCREMENT =3D 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 =3D 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.=20


Ed=20

-----Original Message-----
From: dpgirago@stripped [mailto:dpgirago@stripped]=20
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=20
> database that currently has 935 records in it but because I have=20
> deleted a few the current number used for NEW records is 938 :) How=20
> can I get it to count the records and assign a record number based on=20
> 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@stripped

AFAIK, you need to drop and then recreate the auto-increment field,=20
otherwise you'll get holes when you delete a record.

David