SQL Mass Reordering of rows
ajax, sql, sql-update
Solution
Order should not be your primary key; do the updates by using the primary key in the where clause.
You can do it all in one query using a fairly long CASE statement, if you really want to. Example:
UPDATE foo
SET order = CASE order
WHEN 1 THEN 2
WHEN 2 THEN 3
WHEN 3 THEN 4
WHEN 4 THEN 5
END
WHERE order IN (1,2,3,4)
(Remember that SQL statements behave as if they change all values simultaneously, so that will not do something like change 1 to 2, then to 3, etc.)
Problem
I have a table with a list of records, and a column called order. I have an AJAX script to drag and drop the table rows up or down which I want to use to perform a query, reordering the rows as they have been dragged. In the PHP, I perform a query to get the current order of the records. eg 1, 2, 3 ,4 The AJAX function passes the new order after the drag/drop is complete, eg 3, 1, 2, 4 Is there a simple way to re-order the records in one go, based on the new values? The only other alternative I can see is looping through UPDATE statements eg SET order = 1 where order = 3 But surely this would result in 2 records having the same value? Apologies, I know this description may be slightly confusing.