SQL DELETE与JOIN另一个表在WHERE条件 [英] SQL DELETE with JOIN another table for WHERE condition
本文介绍了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
或UPDATE
引用受影响的表.
Due to the locking implementation issues, MySQL
does not allow referencing the affected table with DELETE
or UPDATE
.
您需要在此处输入JOIN
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条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文