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

Answer 1

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         )