hi guys

I need some help to alter a large table. The table I have consists of 10,000+ entries and the unique field is the "ID" field. I need to change this field so the numbers are consecutive. For example,

Current ID table entries >>> 1,4,6,8,9,10,15,19......

Needed ID table entries >>> 1,2,3,4,5,6,7,8,9......

I know it probably involves using the update query but I can't find anything on the net which meets my needs. Can anyone help?

cheers!

A bit of information about the system might help. I can think of a couple of ways of doing it but it may cause a bit of downtime and or might cause other things to break. If another table uses these ids as a primary key then this gets more complicated.


One simple way would simply be pull all rows out of the table, delete all entries in the table, then put the rows back in while using a counter to set the ids.

As Guy Martin posted at http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html
For those that are looking to "reset" the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

(Keep in mind that DROPping that column will remove all existing data, so if you have exterior resources that rely on that data, or the numbers that are already there, you may break the link. Also, as with any major structure change, it's a good idea to backup your table BEFORE you make the change.)

As Guy Martin posted at http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html

Oh wow! That done the job.

cheers mate!