views:

230

answers:

3

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

A: 

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

  )
Philippe Leybaert
+4  A: 

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
        )
Quassnoi
Yes, for MySQL, this is the right solution. +1
Roland Bouman
A: 

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?

Dirk