Updating display order of multiple MySQL rows in one or very few queries

Question

I have a table with say 20 rows each with a number for display order (1-20).

SELECT * FROM `mytable` ORDER BY `display_order` DESC;

From an admin area you can drag the rows around or type a new number manually for each row.

Surely it's not good to loop over an UPDATE query for every row, what's an alternative in one or very few queries suitable for updating one cell in 20 rows or even more, 50-200+?

---

Edit: A lot of good responses and ideas. I might expand on the ideas I've considered so far:

One array string: I could have the order in a string listing the unique row IDs in the order I want - eg rows 1,9,2,6,23. When the order is updated, a hidden field updates with JavaScript and adds that to the database or a text file when complete:

UPDATE `my_dispaly_order_table` SET `display_order`='1,9,2,6,23';

Update each row individually: This is what I was trying to avoid but it would only be changed very infrequently so 20-30 calls in one hit once a week or month might not be a problem so simply calling UPDATE on each row is what I usually do:

UPDATE `mytable` SET `display_order`='1' WHERE `rowId` = 1;
UPDATE `mytable` SET `display_order`='2' WHERE `rowId` = 9;
UPDATE `mytable` SET `display_order`='3' WHERE `rowId` = 2;
UPDATE `mytable` SET `display_order`='4' WHERE `rowId` = 6;
UPDATE `mytable` SET `display_order`='5' WHERE `rowId` = 23;

Answer 1

soulmerge's answer made me think and I think this is a better solution. What you need to do is select the rows with the id using IN() and then use CASE to set the value.

UPDATE mytable SET display_order =
  CASE id
    WHEN 10 THEN 1
    WHEN 23 THEN 2
    WHEN 4 THEN 3
  END CASE
WHERE id IN (10, 23, 4)

I have a need for this in my current app. In PHP, I'm getting a serialized (and ordered) set of id's from jQuery UI's built-in Sortable feature. So the array looks like this:

$new_order = array(4, 2, 99, 15, 32); // etc

To generate the single MySQL update query, I do this:

$query = "UPDATE mytable SET display_order = (CASE id ";
foreach($new_order as $sort => $id) {
  $query .= " WHEN {$id} THEN {$sort}";
}
$query .= " END CASE) WHERE id IN (" . implode(",", $new_order) . ")";

The "implode" at the end just gives me my ID list for IN(). This works beautifully for me.