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

Hi Jason,

Jason Pruim wrote:
> 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!
>

Actually, it doesn't make sense and for the very reason you are trying
to use it. At some point in history you had a record # 936. Because that
record once existed, there may have been one or several things
associated with it. Imagine the confusion that would ensue if the Social
Security administration recycled an already issued number just as soon
as the person using it died.

The safest thing to do is to pretend that the auto-incrementing field is
an internal, non-editable field. Should you have gaps in your auto-inc
values treat them as normal conditions of having an active database.

For another instance, assume that you are auto-incrementing the serial
numbers to various items in an inventory control system. If an item is
destroyed or taken out of use, you probably want to move that record
from an "activeitems" table to some other location. Would you want to
re-issue those numbers to newly purchased items just to fill in the gaps
in the "activeitems" table? Of course not.

Now, with the understanding that doing this on a regular basis would be
wrong, here is how to do it anyway: Use the "auto_increment=" option to
an ALTER TABLE statement like this

ALTER TABLE mydata AUTO_INCREMENT=936;

(alter table)
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
(for the definition of "table option")
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html

On Aug 29, 2007, at 2:30 PM, Shawn Green wrote:

> Hi Jason,
>
> Jason Pruim wrote:
>> 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!
>
> Actually, it doesn't make sense and for the very reason you are
> trying to use it. At some point in history you had a record # 936.
> Because that record once existed, there may have been one or
> several things associated with it. Imagine the confusion that would
> ensue if the Social Security administration recycled an already
> issued number just as soon as the person using it died.
>
> The safest thing to do is to pretend that the auto-incrementing
> field is an internal, non-editable field. Should you have gaps in
> your auto-inc values treat them as normal conditions of having an
> active database.
>
> For another instance, assume that you are auto-incrementing the
> serial numbers to various items in an inventory control system. If
> an item is destroyed or taken out of use, you probably want to move
> that record from an "activeitems" table to some other location.
> Would you want to re-issue those numbers to newly purchased items
> just to fill in the gaps in the "activeitems" table? Of course not.
>
> Now, with the understanding that doing this on a regular basis
> would be wrong, here is how to do it anyway: Use the
> "auto_increment=" option to an ALTER TABLE statement like this
>
> ALTER TABLE mydata AUTO_INCREMENT=936;
>
> (alter table)
> http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
> (for the definition of "table option")
> http://dev.mysql.com/doc/refman/5.0/...ate-table.html

I see what you are getting at with this, and have decided that
mucking around with auto incrementing values doesn't exactly fit in
with the way databases were designed to work.

Somehow though, I still need to supply this whether I end up adding a
"Record" number field in the database, and then through php (The way
the database is going to be accessed) assigned a record number to
that field based on the total rows, and display that number rather
then the internal record number.

This is getting complicated



>
> --
> Shawn Green, Support Engineer
> MySQL Inc., USA, www.mysql.com
> Office: Blountville, TN
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ /
> / /|_/ / // /\ \/ /_/ / /__
> /_/ /_/\_, /___/\___\_\___/
> <___/
> Join the Quality Contribution Program Today!
> http://dev.mysql.com/qualitycontribution.html
>
> --
> 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

Jason Pruim wrote:
>
> <snip>
>
> I see what you are getting at with this, and have decided that mucking
> around with auto incrementing values doesn't exactly fit in with the way
> databases were designed to work.
>
> Somehow though, I still need to supply this whether I end up adding a
> "Record" number field in the database, and then through php (The way the
> database is going to be accessed) assigned a record number to that field
> based on the total rows, and display that number rather then the
> internal record number.
>
> This is getting complicated
>

The concept of sequential numbers only applies to people. If a row has a
unique identifier that's all you need to know to work with it.

For instance, if you have a list of 26 last names where each name starts
with a different letter of the alphabet. You could enter them in any
order you want and their ID values will be in apparently random order.
However to see the names sorted, you apply an ORDER BY clause to your
query. Now, the A name is #1 and the Z name is #26. Reverse that with
ORDER BY ... desc and the A name will be #26 and the Z name will be #1
in your output. What if you wanted to list just those names after 'K'?
Now the L name becomes #1. I hope this makes it clear that the concept
of sequential numbers is only nice for people. Computers and databases
don't need them to function well.

When you need to supply sequential numbers (as in popularity ranks) the
easiest way to do it is to return an ordered list then number them as
you output them with your client application.

--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html

Do you mean you want to be able to display the record number as sorted by
the auto-increment field, rather than the auto-increment field itself? Or do
you just want the total number of records? Or do you just want the highest
current value of the auto-increment field?

The latter two are easy:

SELECT COUNT(*) FROM table;

SELECT MAX(auto_inc) FROM table;

Offhand, I do not know how to do the first.

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 3:16 PM
> To: Shawn Green
> Cc: MySQL List
> Subject: Re: Reset a auto increment field?
>
>
> On Aug 29, 2007, at 2:30 PM, Shawn Green wrote:
>
> > Hi Jason,
> >
> > Jason Pruim wrote:
> >> 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!
> >
> > Actually, it doesn't make sense and for the very reason you are
> > trying to use it. At some point in history you had a record # 936.
> > Because that record once existed, there may have been one or
> > several things associated with it. Imagine the confusion
> that would
> > ensue if the Social Security administration recycled an already
> > issued number just as soon as the person using it died.
> >
> > The safest thing to do is to pretend that the auto-incrementing
> > field is an internal, non-editable field. Should you have gaps in
> > your auto-inc values treat them as normal conditions of having an
> > active database.
> >
> > For another instance, assume that you are auto-incrementing the
> > serial numbers to various items in an inventory control system. If
> > an item is destroyed or taken out of use, you probably want
> to move
> > that record from an "activeitems" table to some other location.
> > Would you want to re-issue those numbers to newly purchased items
> > just to fill in the gaps in the "activeitems" table? Of course not.
> >
> > Now, with the understanding that doing this on a regular basis
> > would be wrong, here is how to do it anyway: Use the
> > "auto_increment=" option to an ALTER TABLE statement like this
> >
> > ALTER TABLE mydata AUTO_INCREMENT=936;
> >
> > (alter table)
> > http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
> > (for the definition of "table option")
> > http://dev.mysql.com/doc/refman/5.0/...ate-table.html
>
> I see what you are getting at with this, and have decided that
> mucking around with auto incrementing values doesn't exactly fit in
> with the way databases were designed to work.
>
> Somehow though, I still need to supply this whether I end up
> adding a
> "Record" number field in the database, and then through php (The way
> the database is going to be accessed) assigned a record number to
> that field based on the total rows, and display that number rather
> then the internal record number.
>
> This is getting complicated
>
>
>
> >
> > --
> > Shawn Green, Support Engineer
> > MySQL Inc., USA, www.mysql.com
> > Office: Blountville, TN
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ /
> > / /|_/ / // /\ \/ /_/ / /__
> > /_/ /_/\_, /___/\___\_\___/
> > <___/
> > Join the Quality Contribution Program Today!
> > http://dev.mysql.com/qualitycontribution.html
> >
> > --
> > 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
>
>