jpajk
5/9/2017 - 6:00 PM

How do I efficiently reorder my tables

How do I efficiently reorder my tables

You need to do this in two steps:

UPDATE MyTable 
   SET `Order` = `Order` + 1 
 WHERE `Order` > (SELECT `Order` 
                    FROM MyTable 
                   WHERE ID = <insert-after-id>);
...which will shift the order number of every row further down the list than the person you're inserting after.

Then:

INSERT INTO MyTable (Name, `Order`)
VALUES (Name, (SELECT `Order` + 1 FROM MyTable WHERE ID = <insert-after-id>));
To insert the new row (assuming ID is auto increment), with an order number of one more than the person you're inserting after.