CODEWITHSUNDEEP
mysql
Up Here
Up Here

Reputation: 91

How to use order by in update syntax on MySQL

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.

Upvotes: 5

Views: 5446

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31832

MySQL doesn't allow to use ORDER BY in an UPDATE statement with a JOIN. But you can avoid the JOIN by splitting your statement in two:

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

A tricky way to keep it in one statement without a JOIN is to initialize @i in the ORDER BY clause:

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

Upvotes: 4

1000111
1000111

Reputation: 13519

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.

Upvotes: 7

Related Questions