MySQL：您无法指定目标表“任务”用于 FROM 子句中的更新

Question

我在运行以下查询时遇到 MySQL 错误“您无法在 FROM 子句中指定要更新的目标表“任务””：

DELETE FROM tasks
WHERE tasks.id IN 
(
SELECT tasks.id
FROM tasks 
    JOIN deadlines ON deadlines.id = deadline_id
WHERE DATE_ADD(tasks.created_at, INTERVAL deadlines.duration DAY) <= NOW()
)

我该如何管理此问题？

谢谢！

Answer 1

您可以将其包装在子查询中，如下所示。问题是 MySQL 无法更新它也在查询的行。这将使 MySQL 隐式地使用临时表来存储要删除的 id。

DELETE FROM tasks
WHERE tasks.id IN 
(
SELECT id FROM
(
SELECT tasks.id
FROM tasks 
    JOIN deadlines ON deadlines.id = deadline_id
WHERE DATE_ADD(tasks.created_at, INTERVAL deadlines.duration DAY) <= NOW()
) AS taskstodelete
)

Answer 2

这是因为您多次指定任务表。尝试：

DELETE FROM tasks
USING deadlines
WHERE deadlines.id = tasks.deadline_id
AND DATE_ADD(tasks.created_at, INTERVAL deadlines.duration DAY) <= NOW()