Recently I came across a very interesting issue regarding SQL databases that contain ordered information (e.g. imagine a table with TV channel, where each channel has an index: 1,2,3 and so on), that got messed up – positions were out of order and there are “holes” in the positioning.

In the perfect scenario such table would look like this:

• id – The unique identifier of the channel
• ordering – The order of the channel on your TV remote

However, a recent bug in one of the applications that are maintaining, caused data to be messed up -“holes” started appearing in the “ordering” column as you “delete” channels.

This caused bugs that are hard to debug, related to functionality for reordering channels, getting channels count, etc. After we patched the original issue that is causing the holes in ordering, we had multiple approaches on how to fix the other affected features, affected by the messed up ordering.

We could either modify every single feature that relies on the “ordering” column to be okay with holes (not very feasible since there are many features that rely on correct “ordering”), or we could implement a mechanism for correcting the data.

We used the last approach. Using a simple SQL query that iterates all rows and increments an “ordering” index in memory. Finally, the result of this incrementing selection is treated as a subquery in a “UPDATE” SQL operation.

Here’s the final code that we build to handle this:

SET @channelPosition := 0;
UPDATE channels c1
INNER JOIN
(SELECT c.id, @channelPosition := @channelPosition + 1 AS channelPosition
  FROM channels c
  ORDER BY c.ordering) c2
ON c2.id = c1.id
SET c1.ordering = c2.channelPosition;

Final result looks like this:

Hope you find this useful. Do you have suggestions for better ways to approach this issue?