MySQL SQL DELETE 与 JOIN 另一个表的 WHERE 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1980738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL DELETE with JOIN another table for WHERE condition
提问by hsz
I have to delete rows from guide_categorythat have no relation with guidetable (dead relations).
我必须删除guide_category与guide表无关的行（死关系）。
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)
Error:
错误：
You can't specify target table 'guide_category' for update in FROM clause
您不能在 FROM 子句中为更新指定目标表“guide_category”
回答by Quassnoi
Due to the locking implementation issues, MySQLdoes not allow referencing the affected table with DELETEor UPDATE.
由于锁定实现问题，MySQL不允许使用DELETE或引用受影响的表UPDATE。
You need to make a JOINhere instead:
你需要在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
or just use a NOT IN:
或者只是使用一个NOT IN：
DELETE
FROM guide_category AS gc
WHERE id_guide NOT IN
(
SELECT id_guide
FROM guide
)
回答by Dirk
I think, from your description, the following would suffice:
我认为，根据您的描述，以下内容就足够了：
DELETE FROM guide_category
WHERE id_guide NOT IN (SELECT id_guide FROM guide)
I assume, that there are no referential integrity constraints on the tables involved, are there?
我假设所涉及的表没有参照完整性约束，是吗？
回答by user2384628
Try this sample SQL scripts for easy understanding,
试试这个示例 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
Your case is:
你的情况是：
DELETE pgc
FROM guide_category pgc
LEFT JOIN guide g
ON g.id_guide = gc.id_guide
WHERE g.id_guide IS NULL
回答by Philippe Leybaert
How about:
怎么样：
DELETE guide_category
WHERE 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
)
