What is the best way to reorder 1000 rows at one shot

Question

I have 1000 records in a table which holds position field starting from 1 to 1000. Now I want to implement the reorder functionality for 1000 records. Suppose, If I move the 1000th record to 1st position then the 1st record should move to 2nd positon, 2nd record move to 3rd position and 999th record move to 1000th position.

NOTE: I am showing 20 records per page.

I have implemented the reorder functionality using jqGrid drag and drop plugin. Using this technique it is very simple to update 20 records position at once. On the MySQL side, I will fire 20 update queries to update the 20 records position.

Now, I want to have a textbox field in the position column which holds the current record position. So that, user can move any record to any position by entering the position number in the text field regardless of drag and drop. Suppose, I am in the 50th page and I want to move the 1000th record to 1st position, I will enter the position number in the 1000th position textfield as 1. Once I entered the position number, the reorder logic should take place as I said in the first paragraph.

Now, anyone please tell me how can I update 1000 records at once? and what will be the MySQL load? What is the best way to achieve this functionality?

NOTE: I don't want to fire 1000 update queries (i.e. to avoid MySQL deadlock condition) as I did in the drag and drop functionality.

Thanks for anyone help in advance.

Answer 1

Hundreds of updates is kind of ridiculous. Try something like this:

UPDATE Records SET
SequenceNumber = SequenceNumber + 1
WHERE SequenceNumber >= @Lowbound AND SequenceNumber <= @UpperBound;

UPDATE Records SET
SequenceNumber = @Lowbound
WHERE ID = @SelectedId;