MySQL: delete parent records with no children

Question

How do I write a query that deletes records from a parent table for which no children exist?

I have a table resume and a table personal_skill that has a resume_id field to refer the resume table. I need to delete all the records in the resume table for which there is no personal_skill record with such resume_id.

I tried to do it like this:

    DELETE
    FROM
      resume
    WHERE
      version = 0
      AND NOT EXISTS (SELECT NULL
                      FROM
                        personal_skill x
                      WHERE
                        x.resume_id = id)

but this query fails cause I can't use the table I'm deleting from in SELECT part.

Answer 1

Your try plainly works here:

 DELETE 
 FROM resume     
 WHERE version = 0
   AND NOT EXISTS (
     SELECT id                       
     FROM personal_skill x
     WHERE x.resume_id = resume.id
  );

If it doesn't, perhaps the plain id was the problem (does personal_skill have an personal_skill.id column?).

An alternative is:

 DELETE resume  
 FROM resume 
 LEFT JOIN personal_skill 
    ON personal_skill.resume_id=resume.id 
 WHERE personal_skill.id IS NULL; -- or any non-NULLable column from personal_skill