MySQL:您无法在FROM子句中指定目标表“任务"进行更新 [英] MySQL: You can't specify target table 'tasks' for update in FROM clause

问题描述

我遇到了MySQL错误您无法在FROM子句中指定目标表'tasks'进行更新"，并运行以下查询:

I have got MySQL error "You can't specify target table 'tasks' for update in FROM clause" running the following query:

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

我该如何管理?

感谢！

推荐答案

您可以像这样将其包装在子查询中.问题是MySQL无法更新它也在查询的行.这将使MySQL隐式使用一个临时表来存储您要删除的ID.

You can wrap it in a subquery like so. The issue is that MySQL can't update rows that it's also querying. This will make MySQL use a temporary table implicitly to store the ids you want to delete.

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
)

这篇关于MySQL:您无法在FROM子句中指定目标表“任务"进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！