使用JOIN另一个表格作为WHERE条件的SQL DELETE操作

Question

我需要从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子句中指定目标表“guide_category”进行更新

- hsz

---

这是因为MySQL中的一个恶劣限制。请参阅Quassnoi的答案 - 他有正确的解决方案来解决这个问题。 - Roland Bouman

Answer 1

由于锁定实现问题，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
        )

- Quassnoi

---

如果您正在使用TSQL，请使用“DELETE gc”而不是“DELETE gc.*”。 - Gezim

对于MySQL > 5.0，应使用DELETE gc替代gc.*。 - Daniel W.

但是哪一个更快？ - duleshi

@duleshi：当正确索引时，相同的。 - Quassnoi

@duleshi：根据您的数据库，其中一种方法可能比另一种方法快得多。根据我的经验，在Postgres数据库中，即使所有索引都已就位，使用LEFT JOIN和IS NULL很快，而NOT IN则很慢。 - Michael Piefel

@MichaelPiefel：通常来说，带有NOT NULL和NOT IN的LEFT JOIN在语义上是不同的。 - Quassnoi

Answer 2

根据您的描述，我认为以下内容应该足够:

DELETE FROM guide_category 
WHERE id_guide NOT IN (SELECT id_guide FROM guide)

我假设涉及的表没有参照完整性约束，是吗？

- Dirk

---

这会有点慢，因为它将为每个条目执行一个(SELECT id_guide FROM guide)。 - Toumi

这实际上取决于，@Toumi。查询规划器/优化器可以在幕后做很多事情。 - Dirk

Answer 3

尝试使用这些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

- user2384628

---

那才是答案。简单而坚实。干得好先生。 - katsos

@NikosKeyz 这不就是与被采纳的答案完全相同的答案吗？ - sstn

@sstn 不好意思，被采纳的答案对我没用。这个是最适合我的答案。 - katsos

Answer 4

这样怎么样：

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

  )

- Philippe Leybaert

---

这是原始查询，并且在派生表达式中引用了原始表格，存在完全相同的问题。 - siride