本文介绍了SQL DELETE 与 JOIN 另一个表的 WHERE 条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我必须从 guide_category 中删除与 guide 表无关的行(死关系).
I have to delete rows from guide_category that have no relation with guide table (dead relations).
这是我想要做的，但它当然不起作用.
Here is what I want to do, but it of course does not work.
DELETE FROM guide_category AS pgc
WHERE pgc.id_guide_category IN (SELECT id_guide_category
FROM guide_category AS gc
LEFT JOIN guide AS g ON g.id_guide = gc.id_guide
WHERE g.title IS NULL)
错误:
不能在 FROM 子句中指定目标表 'guide_category' 进行更新
You can't specify target table 'guide_category' for update in FROM clause
推荐答案
由于锁定实现问题，MySQL 不允许使用 DELETE 或 引用受影响的表代码>更新.
Due to the locking implementation issues, MySQL does not allow referencing the affected table with DELETE or UPDATE.
你需要在这里创建一个 JOIN :
You need to make a JOIN here instead:
DELETE gc.*
FROM guide_category AS gc
LEFT JOIN
guide AS g
ON g.id_guide = gc.id_guide
WHERE g.title IS NULL
或者只是使用NOT IN:
DELETE
FROM guide_category AS gc
WHERE id_guide NOT IN
(
SELECT id_guide
FROM guide
)
这篇关于SQL DELETE 与 JOIN 另一个表的 WHERE 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！