How to use order by in update syntax on MySQL

Question

I want to use order by in this query:

update mytable cross join
       (select @i := 0) params
    set mydate = mydate + interval 10 * (@i := @i + 1) hour;

My reference is from this question, as in that question let say I want to order the PID descending,

update mytable cross join
           (select @i := 0) params
        set mydate = mydate + interval 10 * (@i := @i + 1) hour order by PID desc;

But with that query I got ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY

The original query is working fine but I can't use order by in it.

Answer 1

Something like below might do:

UPDATE mytable MT 
INNER JOIN
(
 SELECT 
 *,
 @i := @i + 1 AS paramNumber
 FROM 
 mytable 
 cross join(select @i := 0) params
 ORDER BY PID DESC
) AS t
ON MT.PID = t.PID
SET MT.mydate = MT.mydate + INTERVAL (10 * (t.paramNumber)) HOUR 

14.2.11 UPDATE Syntax

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.