Renumbering an "ordering" field in mysql

Sometimes you have a field used to order the presentation of rows to users. This might be to sequence tasks or (as used in Joomla) to decide the presentation order of articles.

As you use the field you can end up with odd numbers and, ideally you want a nice separated sequence so you can move or insert new tasks easily. To do that you need to be able to renumber the sequence, preserving the original ordering.

Here's how you can use an SQL statement to achieve this:

I'll create a test case - a table of tasks with an ordering field to decide the order they will take place in.

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE tasks (
    -> id int(11) not NULL auto_increment,
    -> label varchar(128) not NULL,
    -> ordering int(11) not NULL default 0,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tasks (label, ordering) VALUES
    -> ("Pick up kids", 1),
    -> ("Play in park", 2),
    -> ("Feed kids", 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Oops, I forgot to get the kids to wash their hands and I can't shuffle my tasks easily because there are no gaps in the numbers so I need to re-number them. I'll make them go 10, 20 etc.

mysql> SET @ordering_inc = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @new_ordering = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tasks SET 
    -> ordering = (@new_ordering := @new_ordering + @ordering_inc)
    -> ORDER BY ordering ASC;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM tasks;
+----+--------------+----------+
| id | label        | ordering |
+----+--------------+----------+
|  1 | Pick up kids |       10 | 
|  2 | Play in park |       20 | 
|  3 | Feed kids    |       30 | 
+----+--------------+----------+
3 rows in set (0.00 sec)

Now I can insert the "wash kids hands" task easily and renumber again:

mysql> INSERT INTO tasks (label, ordering) VALUES 
    -> ("Wash kids hands", 15);
Query OK, 1 row affected (0.00 sec)

mysql> SET @new_ordering = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tasks SET 
    -> ordering = (@new_ordering := @new_ordering + @ordering_inc) 
    -> ORDER BY ordering ASC;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM tasks ORDER BY ordering;
+----+-----------------+----------+
| id | label           | ordering |
+----+-----------------+----------+
|  1 | Pick up kids    |       10 | 
|  4 | Wash kids hands |       20 | 
|  2 | Play in park    |       30 | 
|  3 | Feed kids       |       40 | 
+----+-----------------+----------+
4 rows in set (0.00 sec)

Notice that I don't use the id column for my ordering. Keep the meaning of columns clear. The id is to identify the row. It is NOT to order the row - For that I create and use the "ordering" column. Keeping things simple like this will avoid nightmares.

Here is a real example from a script used to update a number of Joomla sites needing a similar menu addition:

--
-- Conditionally insert the staff menu item
-- jos_menu is aliased to jm to work around
-- mysql limitation - ERROR 1093 (HY000)

-- Make sure the ordering has a gap to stick our new item in
SET @ordering_inc = 10;
SET @new_ordering = 0;
UPDATE jos_menu SET
    ordering = (@new_ordering := @new_ordering + @ordering_inc)
    WHERE menutype = 'staff-menu'
    ORDER BY ordering ASC;
    
REPLACE INTO `jos_menu` SET
    `id` = (
SELECT
        IFNULL(`id`,'O')
    FROM (
    SELECT `id` FROM `jos_menu`
    WHERE
    menutype='staff-menu' AND
    link='index.php?option=com_joodb<bl=pp_pets_list_view') AS jm),
    `menutype` = 'staff-menu',
    `name` = 'Pets',
    `alias` = 'pets',
    `link` = 'index.php?option=com_joodb<bl=pp_pets_list_view',
    `type` = 'url',
    `published` = '1',
    `parent` = '0',
    `componentid` = '0',
    `sublevel` = '0',
    `ordering` = (
SELECT
        `ordering`+1
    FROM (
    SELECT `ordering` FROM `jos_menu`
    WHERE
        menutype='staff-menu' AND `name`='Customers') AS jm),
    `checked_out` = '0',
    `checked_out_time` = '0000-00-00 00:00:00',
    `pollid` = '0',
    `browserNav` = '0',
    `access` = '0',
    `utaccess` = '0',
    `params` = 'menu_image=-1\n\n',
    `lft` = '0',
    `rgt` = '0',
    `home` = '0';

-- tidy up the ordering
SET @new_ordering = 0;
UPDATE jos_menu SET
    ordering = (@new_ordering := @new_ordering + @ordering_inc)
    WHERE menutype = 'staff-menu'
    ORDER BY ordering ASC;