CODEWITHSUNDEEP
mysqlsql
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

MySQL UPDATE with JOIN and ORDER BY

Trying to run the following query:

UPDATE task_schedule
JOIN tasks ON (tasks.taskid=task_schedule.taskid)
SET task_schedule.user_position = @counter := @counter + 1
WHERE tasks.userid_owner = 6
ORDER BY task_schedule.product_position asc, task_schedule.productid asc

But getting a Incorrect usage of UPDATE and ORDER BY error.

Anyway I could get around this?

Upvotes: 0

Views: 128

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

ORDER BY is not allowed with the multi-table syntax on UPDATE. Here is one way to fix the query:

UPDATE task_schedule
SET task_schedule.user_position = @counter := @counter + 1
WHERE exists (select 1
              from tasks t
              where t.userid_owner = 6 and
                    t.taskid = task_schedule.taskid
             )
ORDER BY task_schedule.product_position asc, task_schedule.productid asc

Upvotes: 2

Related Questions