SQL DELETE with JOIN another table for WHERE condition

Question

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    Error        You can t specify target table  guide category  for update in FROM clause

User · Accepted Answer

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
        )

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] SQL DELETE with JOIN another table for WHERE condition

Examples related to mysql

Examples related to sql

Examples related to delete-row

Examples related to sql-delete

Examples related to mysql-error-1093