How do I reset sequence numbers to become consecutive?

Question

I've got a mysql table where each row has its own sequence number in a "sequence" column. However, when a row gets deleted, it leaves a gap. So...

1
2
3
4

...becomes...

1
2
4

Is there a neat way to "reset" the sequencing, so it becomes consecutive again in one SQL query?

Incidentally, I'm sure there is a technical term for this process. Anyone?

UPDATED: The "sequence" column is not a primary key. It is only used for determining the order that records are displayed within the app.

Answer 1

If the field is your primary key...

...then, as stated elsewhere on this question, you shouldn't be changing IDs. The IDs are already unique and you neither need nor want to re-use them.

Now, that said...

---

Otherwise...

It's quite possible that you have a different field (that is, as well as the PK) for some application-defined ordering. As long as this ordering isn't inherent in some other field (e.g. if it's user-defined), then there is nothing wrong with this.

You could recreate the table using a (temporary) auto_increment field and then remove the auto_increment afterwards.

I'd be tempted to UPDATE in ascending order and apply an incrementing variable.

SET @i = 0;
UPDATE `table`
   SET `myOrderCol` = @i:=@i+1
 ORDER BY `myOrderCol` ASC;

^{(Query not tested.)}

It does seem quite wasteful to do this every time you delete items, but unfortunately with this manual ordering approach there's not a whole lot you can do about that if you want to maintain the integrity of the column.

You could possibly reduce the load, such that after deleting the entry with myOrderCol equal to, say, 5:

SET @i = 5;
UPDATE `table`
   SET `myOrderCol` = @i:=@i+1
 WHERE `myOrderCol` > 5
 ORDER BY `myOrderCol` ASC;

^{(Query not tested.)}

This will "shuffle" all the following values down by one.