MySQL 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,
  `sort_index` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `foo` (`bar_id`, `sort_index`) VALUES
 (1,1),(1,2),(1,3),(1,4),
 (2,1),(2,2),(2,3),(2,4),(2,5);

I want to be able to do the following in the most efficient manner:

1. Move a foo entry to a given position (scoped by the bar_id)
2. Ensure that the sort_index is always 1 indexed and has no gaps
3. You should be able to move items to the beginning and end of the list and rule #2 should still be applied
4. It should be done entirely in queries and as few as possible (as the sets could be very large and looping over them doing individual UPDATEs is not ideal)

To clarify what I'm trying to do, lets assume the table was empty so we have the following data:

id | bar_id | sort_index
1  | 1      | 1
2  | 1      | 2
3  | 1      | 3
4  | 1      | 4    
5  | 2      | 1
6  | 2      | 2
7  | 2      | 3
8  | 2      | 4
9  | 2      | 5

Then if we were to do the following moves

• foo 1 to sort_index 3
• foo 7 to sort_index 1
• foo 5 to sort_index 5

We should get the following data:

id | bar_id | sort_index
1  | 1      | 3
2  | 1      | 1
3  | 1      | 2
4  | 1      | 4    
5  | 2      | 5
6  | 2      | 2
7  | 2      | 1
8  | 2      | 3
9  | 2      | 4

And SELECT * FROM foo ORDER BY bar_id, sort_index; gives us:

id | bar_id | sort_index
2  | 1      | 1
3  | 1      | 2
1  | 1      | 3
4  | 1      | 4  
7  | 2      | 1
6  | 2      | 2
8  | 2      | 3
9  | 2      | 4
5  | 2      | 5

From stackoverflow

• 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.)

  DEfusion : That's pretty snazzy and almost works perfectly. However it doesn't quite work if I want to move an item to the end of the list, as I end up with 2 with the same finishing sort_index and none filling the old sort_index (e.g. if I move an item from 3 to 5 I end up with 2 with sort_index of 5 and nothing with a 3).

  DEfusion : Actually there are some situations (like moving to the end of the list) where you need to use this instead: UPDATE foo SET sort_index = sort_index - 1 WHERE bar_id == b AND sort_index > s1 AND sort_index <= s2

  David : Ah yes, of course. In fact, it's not just when you're moving it to the end of the list, it's whenever you're moving it forward.