How to fix SQL indexes to remove spaces/jumps?

Question

I have a table where entries are listed and sometimes edited and removed which creates a space. Example: 1,2,4,5,6,8,10 I would like to have them in order example: 1,2,3,4,5,6,7

Is there any code to do this qick and painless?

Thank you.

You just should not do that. There is nothing to be gained and possible a lot to be lost from such actions. — Mchl, Jan 25 '11 at 09:38
Shoudn't this list of entries represented in another table? Maybe see [this question](http://stackoverflow.com/questions/287794/multivalued-attributes-in-databases). — pascal, Jan 25 '11 at 09:56
Some of the answers and comments seem to assume you are saying that the primary key field changes. Is this correct, or is it a non-key field? Either way however, a different approach would probably be a good idea. — Daniel Renshaw, Jan 25 '11 at 09:59
Hint for other people with a similar problem: If you were using a RDBMS with support for analysis functions (e.g. Oracle or SQL Server), you could create a view on your table with row_number (order by number_with_gaps) as a column, preserving the original valued but presenting the ordered list without spaces. — dataduck, Jan 25 '11 at 12:30

score 0 · Answer 1 · edited Jan 25 '11 at 10:25

If you have any foreign key constraints then you'll need to use ON UPDATE CASCADE (something I tend to avoid like plague).

Is there a good reason to ensure these numbers are sequential or is just for aesthetics?

There's no problem having keys 'missing' - the database cares very little.

IF you really need to do this then:

SET @indx = 1;
UPDATE `table` SET `field` = (@indx:=@indx+1) ORDER BY `field` ASC;

Have fun...

How to fix SQL indexes to remove spaces/jumps?

1 Answers1