SQL DELETE 与 JOIN 另一个表的 WHERE 条件 [英] SQL DELETE with JOIN another table for WHERE condition

问题描述

我必须从 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 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！