I have a table with an auto increment ID that runs a lot of INSERTs and DELETEs resulting in big gaps in the numbers. I'm just wondering for the sake of neatness, is there a query that will start with the lowest ID, assign it 1 and work up +1 from there? The ID is not in a relation with any other column in the database.
So it would go from, e.g:
I don't want to reset the auto increment, I know how to do that. I was to actually change the IDs. Or is this considered bad practice?
Although it is not a good practice you could use a query like this:
Let's assume we have a table with two fiels
id is of type INT.
insert into table (id, value) select max(id)+1, 'value' from table
Be aware that with a lot of insert/update there could be problems if more than one queries runs simultaneously.