SQL DELETE with JOIN another table for WHERE condition

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_guideWHERE   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 TABLE2INSERT 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 TABLE1SELECT * FROM TABLE2DELETE 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