mysql - 用另一个查询结果更新行

Question

假设我有一个这样设置的数据库，我想通过将优先级更改为最后一个数字 + 1 来将 Milk 移到最后。

id | item | priority---|------|----------   [...]26 | Milk | 127 | Eggs | 228 | Ham  | 3

So I need to run something like this

UPDATE shopping SET priority = (SELECT priority FROM shopping ORDER BY priority DESC LIMIT 1) + 1 WHERE id = '26'

最后得到这样的结果

id | item | priority---|------|----------   [...]27 | Eggs | 228 | Ham  | 326 | Milk | 4

我如何正确地执行此操作？

Answer 1

UPDATE `shopping` s, (
    SELECT
        MAX(`priority`) AS 'maxPriority',
        MAX(`id`) AS 'maxId'
    FROM `shopping`
) t
SET
    s.`priority` = t.`maxPriority` + 1,
    s.`id` = t.`maxId` + 1
WHERE s.`id`=26
AND s.`priority` != t.`maxPriority`
# this ensures that it's not the one that's
# already holding the max priority