首页 文章

SQL DELETE与JOIN另一个表用于WHERE条件

提问于
浏览 次
70

我必须从 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'

mysql sql delete-row sql-delete mysql-error-1093

4 回答

  • -3

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

    我认为，根据您的描述，以下内容就足够了：

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

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

    回复于
  • 4

    试试这个示例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
    回复于
  • 105

    怎么样：

    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

  )
    回复于

相关问题