john john - 6 months ago 16
MySQL Question

How to execute UPDATE query with JOIN and ORDER?

my query is:

set @csum := 0;
update `aaa` INNER JOIN
`bbb`
ON `bbb`.`id`=`aaa`.`tid`
set `aaa`.`tc` = (@csum := @csum + 1)
WHERE `aaa`.`tid` IN (6,7)
ORDER BY `bbb`.`priority` ASC, `aaa`.`floor` ASC, `aaa`.`id` ASC;


but return error
#1221 - Incorrect usage of UPDATE and ORDER BY


How can i execute this query?
i need sort and update from first to last record

Answer

This is quite tricky. MySQL doesn't allow order by with an update that uses a join. But, your query needs both tables for the order by.

The following select seems to return what you want:

select aaa.*, (@csum := @csum + 1) as csum
from `aaa` join
     `bbb`
     on `bbb`.`id` = `aaa`.`tid` cross join
     (select @csum := 0) params
where `aaa`.`tid` IN (6, 7)
order by `bbb`.`priority` ASC,  `aaa`.`floor` ASC, `aaa`.`id` ASC;

You can now incorporate this into an update:

update aaa join
       (select aaa.*, (@csum := @csum + 1) as csum
        from `aaa` join
             `bbb`
             on `bbb`.`id` = `aaa`.`tid` cross join
             (select @csum := 0) params
        where `aaa`.`tid` IN (6, 7)
        order by `bbb`.`priority` ASC,  `aaa`.`floor` ASC, `aaa`.`id` ASC
      ) aaaa
      on aaaa.id = aaa.id
    set `aaa`.`tc` = csum;