mysql - SQL 错误 : DELETE and SELECT FROM conflict

Question

我对以下 SQL 查询有疑问:

DELETE FROM table1 WHERE uid =
(SELECT table1.uid from table1 INNER JOIN table2 ON table2.user = table1.uid
INNER JOIN table3 ON table3.uid = table2.seminar WHERE table3.end_date < CURDATE()))

错误是:您不能在 FROM 子句中指定要更新的目标表 'table1'

有人知道如何解决这个问题吗？

Answer 1

这可能不是最优雅的方式，但您可以将您的条件选择到临时表中，然后从#MyTempTable 中的位置删除

SELECT table1.uid 
INTO #MyTemp
from table1 INNER JOIN table2 ON table2.user = table1.uid
INNER JOIN table3 ON table3.uid = table2.seminar WHERE table3.end_date < CURDATE())


DELETE FROM table1 
WHERE uid IN
(SELECT uid from #MyTemp)

DROP TABLE #MyTemp