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';
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.