Maintaining/updating record order in mysql

Question

I have a table of records in mySql. I need to maintain an order for them as specified by the user. So I've added a 'position' column.

What would be the SQL statement to update all the records when I move a specific record? I've got something like:

UPDATE items SET position = '2' WHERE id ='4';
UPDATE items SET position = position+1 WHERE position >= '2' AND id != '4';

But the greater than is going to be a less than if the record has moved down. What's the trick? Thanks!

Answer 1

Doing this sort of thing for e.g. sales orders with line numbers maintained by the user, I've found it best to handle it in an array in the BL or UI. Usually they will want to adjust several records, and sometimes want to say "forget it". So the easiest might be to just wait until they hit the "OK" button (or your equivalent) and then write them all back with current ordering.

You may end up dealing with deletions, too, which is another problem you can handle the same way.

Answer 2

I would suggest, at a minimum, using large increments (say 10000) between items. Then just doing an order by. If you need to move 11000 to between 9000 and 10000 then just set it to 9500 and done.

This doesn't eliminate the problem of reordering but it greatly reduces it. At some point you're better off deleting all the rows then readding them with the correct order. Doing updates like you're doing is just too error prone imho.

Answer 3

You might consider using a linked list type of approach, with a key to the next, and maybe the previous item in the list, then you only have to update a few records, instead of all of them.

Answer 4

Interesting! Okay, so there is no easy answer. I thought I was just missing something. I'd thought about maintaining an external array. That sounds like the best idea.

Answer 5

Would something like this do it?

UPDATE items 
SET position = CASE position 
  WHEN $oldpos THEN $newpos 
  ELSE position + SIGN($oldpos-$newpos)
 END
WHERE position BETWEEN LEAST( $newpos, $oldpos ) 
                AND GREATEST( $newpos, $oldpos );

I tested it a couple of times and it seems to work.