Update A Sorting Index Column To Move Items

If I have the following table & data to allow us to use the sort_index for sorting: CREATE TABLE `foo` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `bar_id` INT(11) DEFAULT NULL

Solution 1:

You should be able to do this in a single query: something along the lines of UPDATE foo SET sort_index = sort_index + 1 WHERE bar_id == b AND sort_index < s1 AND sort_index >= s2, where b is the bar_id of the row to be moved, s1 is the current sort_index of that row, and s2 is the the sort_index you want to move it to. Then, you'd just change the sort_index of the row.

You'd probably want to do the two queries inside a transaction. Also, it might speed things up if you created an index on the sort_index using something like CREATE INDEX foo_index ON foo (sort_index).

(By the way, here I'm assuming that you don't want duplicate sort_index values within a given bar_id, and that the relative order of rows should never be changed except explicitly. If you don't need this, the solution is even simpler.)