用于 WHERE 条件的 SQLDELETE 和 JOIN 另一个表

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

214426 次浏览
小开

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


)
小开
最佳答案

Due to the locking implementation issues, MySQL does not allow referencing the affected table with DELETE or UPDATE.

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

or just use a NOT IN:

DELETE
FROM    guide_category AS gc
WHERE   id_guide NOT IN
(
SELECT  id_guide
FROM    guide
)
小开

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?

小开

Try this sample SQL scripts for easy understanding,

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

提交答案