mysql - 如何使用MySQL在更新语句中选择相同的表

Question

在此表中，有一列称为“位置”。此列从 0 开始计数。(第一行的位置为 0，第二行的位置为 1 等)

现在，如果位置 4 的行向上移动，我想将上面的行(位置 3 的行)设置为当前位置。 (因此位置 3 变为位置 4。)

例如:

我有:

id = 12, position = 2
id = 11, position = 3
id = 9, position = 4
id = 8, position = 5

更新后我想要:

id = 12, position = 2
id = 11, position = 4
id = 9, position = 3
id = 8, position = 5

我想使用行的 id 来更新位置。

这是我所拥有的，但它不适用于 MySQL:

UPDATE message
SET position = position + 1
WHERE position = (SELECT position - 1 FROM message WHERE id = 11);

当我尝试运行它时，它给出了以下错误:“SQL 错误(1093):您无法在 FROM 子句中指定用于更新的目标表“消息””。

我该如何解决这个问题？

Answer 1

编辑 - 使用 OP 中的示例数据更新后:

我假设您知道 ID (=11) 并希望数据按照更新后的问题中所示进行更改...您需要更新 2 行才能实现这一点:

UPDATE message m SET
m.position = (CASE WHEN m.id = 11 THEN m.position + 1 ELSE m.position - 1 END)
WHERE m.id = 11 OR m.id =
(SELECT z.li FROM (SELECT MAX (l.id) li FROM message h INNER JOIN message l ON h.position = (l.position - 1) WHERE h.id = 11) z);