How to Reorder Records in a Table with a Single SQL Query?

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:

id	listID	name	position
1	1	cheese	0
2	1	chips	1
3	1	bacon	2
4	1	apples	3
5	1	pears	4
6	1	pie	5

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.

The above is the detailed content of How to Reorder Records in a Table with a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!