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 · 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

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

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