Updating an "order" column MySQL?

Question

What's the best way to update an "order" column?

Say I have an id column with 0 to 9, and order column from 0 to 9.

Currently, it's in the database as: 0 0, 1 1, 2 2, etc

My HTML page posts what it wants as the new order: 0 8, 1 3, 2 6, etc. (This is completely random, decided by the user).

What's the best way to make the update?

I can loop through and run an update for each. I can also create a temporary table with all the pairs, and then update based on a subquery.

But I feel like I'm forgetting something trivial that will do this much faster. Any suggestions?

Thanks, Colin

Answer 1

You have to update each row by itself, there is no way to do a "map" update in sql.

(There are some tricks but they don't really apply/help here anyway.)

Answer 2

If the user really is reordering the items at random, there isn't much you can do. But typically a user might make a small modification to an existing ordering, such as moving one item higher up in the list. In this case you can make a minor speed improvement by relaxing your constraint that the sort order column must contain sequential integers. I discussed a possible idea for this before here and you can read that suggestion if you're interested in the details of how to implement this.

But for lists of size 10 it's probably not worth the extra effort. Just do what you are doing - it'll probably be just fine in practice.