Corey Corey - 1 month ago 10
MySQL Question

Maintaining/updating record order in mysql

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 Source

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.