SQL DELETE with JOIN WHERE条件的另一个表
SQL DELETE with JOIN WHERE条件的另一个表
提问于 2009-12-30 23:27:50
我必须从guide_category中删除与guide表没有关系的行(死关系)。
这是我想要做的，但它当然不起作用。
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子句
中为update指定目标表'guide_category‘
回答 4
Stack Overflow用户
回答已采纳
发布于 2009-12-30 23:31:10
由于锁定实现问题，MySQL不允许使用DELETE或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
)Stack Overflow用户
发布于 2009-12-30 23:35:20
我认为，从你的描述来看，以下内容就足够了：
DELETE FROM guide_category
WHERE id_guide NOT IN (SELECT id_guide FROM guide)我假设所涉及的表上没有引用完整性约束，对吗？
Stack Overflow用户
发布于 2013-05-15 15:03:52
为了便于理解，请尝试以下示例SQL脚本
CREATE TABLE TABLE1 (REFNO VARCHAR(10))
CREATE TABLE TABLE2 (REFNO VARCHAR(10))
--TRUNCATE TABLE TABLE1
--TRUNCATE TABLE TABLE2
INSERT INTO TABLE1 SELECT 'TEST_NAME'
INSERT INTO TABLE1 SELECT 'KUMAR'
INSERT INTO TABLE1 SELECT 'SIVA'
INSERT INTO TABLE1 SELECT 'SUSHANT'
INSERT INTO TABLE2 SELECT 'KUMAR'
INSERT INTO TABLE2 SELECT 'SIVA'
INSERT INTO TABLE2 SELECT 'SUSHANT'
SELECT * FROM TABLE1
SELECT * FROM TABLE2
DELETE T1 FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.REFNO = T2.REFNO你的案例是：
DELETE pgc
FROM guide_category pgc
LEFT JOIN guide g
ON g.id_guide = gc.id_guide
WHERE g.id_guide IS NULL页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1980738
复制相关文章
相似问题