MySQL - Save list order with single update

Question

Is it possible to sort a list and save the order in a single update?

I tried this way:

UPDATE `jos_vm_category`,(SELECT @row:=0) AS init SET @row:=@row+1, list_order=@row ORDER BY `category_name` ASC

but got an error:

1221 - Incorrect usage of UPDATE and ORDER BY

If it's not clear, I need this:

category_id | category_name | list_order
    3       |       A       |       1
    1       |       B       |       2
    2       |       C       |       3

from this:

category_id | category_name | list_order
    1       |       B       |       1
    2       |       C       |       2
    3       |       A       |       3

with a single UPDATE.

So list_order is a field of the table where I have to save the order of rows. (I already have the solution, but have to wait for 2 days, so I'll publish then if nobody answers the question.)

Answer 1

MySql does not permit a ORDER BY with a multiple table update. Docs

You can use a sub query instead:

UPDATE jos_vm_category c
    JOIN (
        SELECT category_id, (@row:=@row+1) rowNum
        FROM jos_vm_category, (SELECT @row:=0) dm
        ORDER BY category_name
    ) rs ON c.category_id = rs.category_id
SET c.list_order = rs.rowNum

Alternatively, you can split them into 2 queries:

SELECT @row:=0;
UPDATE jos_vm_category
SET list_order = (@row:=@row+1)
ORDER BY category_name;