> 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

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

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

Yes, for a MyIsam type table.

Ed

-----Original Message-----
From: Jason Pruim [mailto:japruim@raoset.com]
Sent: Wednesday, August 29, 2007 11:53 AM
To: emierzwa
Cc: dpgirago@mdanderson.org; 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, <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

It would not reset to 901 unless the highest numbered record were 900. It
won't fill in holes.

Since autoincrement fields are typically used as keys linking to other
tables, renumbering existing records is not done often. If you REALLY want
to renumber them all, copy the records to a new table but leave off the
autoincrement field. TRUNCATE the original table and copy back the original
records, supplying NULL for the autoincrement field.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -----Original Message-----
> From: Jason Pruim [mailto:japruim@raoset.com]
> Sent: Wednesday, August 29, 2007 1:53 PM
> To: emierzwa@micron.comemierzwa@micron.com
> Cc: dpgirago@mdanderson.org; 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, <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
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>