Reordering Records with a Single SQL Query

In a table of food items with a "Position" field to order their appearance, a single SQL query can update multiple records based on a change to a single record. This is especially useful for reordering items within a specific list.

Suppose we have a table like this:

To move pears before chips, we can increment the position of all items between them by 1.

However, most solutions involve multiple queries, which can be inefficient. Instead, a single query can be used to reorder the records:

UPDATE my_table
SET position = position   CASE
    WHEN name = 'pears' THEN -3
    ELSE 0
    END
WHERE listID = 1 AND position BETWEEN 1 AND 4;

This formula ensures that pears move to position 1, and all items between pears and chips are incremented accordingly.

To reorder any item, simply replace 'pears' with the name of the item to be moved, and adjust the difference as needed.