MySQL：您无法在FROM子句中指定目标表“任务”进行更新

一尘不染

MySQL：您无法在FROM子句中指定目标表“任务”进行更新

sql

我收到运行以下查询的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()
)

我该如何处理？

谢谢！

阅读 171

2021-03-10

共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
)

2021-03-10