re-serialize and reset auto increment

Fritz

What I'd like to do is clean up my auto increment field and reset auto increment to that new number (field is called record_no)

exp.

4 records

record_no
1
7
8
9

I want to change these so that they are

1
2
3
4

and reset the internal auto increment for the field to 5

Can someone toss me the sql syntax?

tomhath

Only way I know to do it is create another table, copy the records into it (which will renumber the autoincrement column) then drop the old table and rename the new one.

That assumes you haven't used the autoincrement column as a foreign key anywhere to link back to your table, renumbering will make a mess if you did.

I can't think of a good reason to renumber a primary key anyway, it's just a unique number, you shouldn't care what the number happens to be.

Fritz

True... Duping the table will set the auto increment to one greater than the highest used number ....

BUT Surely there must be a statement that

reserialize that field ...
defines the NEXT number.

Help Anyone?

Sxooter

Generally when someone asks this question it is because they confuse the use of an autoincrementing column with a "row number" type of function.

the purpose of an autoincementing column is to provide a unique, non-repeating key that can be used to refer to this record from another table. In a large transactional system it is quite common to have gaps in this field due to things like rolled back transactions or deleted user records. In general it is considered very bad form to change this field by hand, or treat it like a row number, since it only coincidentally happens to go in order.

So, I'll ask you first WHY you feel the need to reset the sequence, and the answer to that question may lead us to a better answer than the ones given so far. But unless you have some pressing need to do so, it would be best to NOT muck around with your autoincrement column, and make sure your code doesn't rely on it being sequential without holes in it.

Fritz

Thanks for the response...

Fair enough you do make some good points...

I have just set up a books table... Since it's new I have deleted many entries... BUT aM using an AI field to show the submission number... It's important for us to note that this is say entry #10

but with the deletions what is finally entry number 10 is attempt number 17 - So yes... I did go in and change this number in the records so as to clean it up. now I have say 20 records with the next auto incre coming in as 29

Is there another way I should approach this?

Meaning Can PHP do a numbering of results from a select statement kind of like the order'd list tag in HTML?

Sxooter

Well, PHP and SQL together can probably do it. What you want to do is sort on something like the date/time the record was recorded, and use limit.

Something like this:

select * from ordertable order by dateentered DESC limit 10,0;

I'm not sure of the syntax of the limit statement in mysql (i.e. if the limit or offset comes first.)

then you can just walk through the result list and use the index number of each result or something like that.

P.s. I love witchvox. It's a nicely done and well maintained site.

miesemer

You can reset the next auto increment number with the following:
ALTER TABLE table_name AUTO_INCREMENT = 1;

Fritz

Greets,

Thanks Sxooter for the kind words about Witchvox.com - now if I can only evolve from this 'php newbie' status... it will be something to see

and thanks miesemer for that code snip.

the other question is looking for a sql update statement that will 'reserializ' all records... say in the ID field.