how to delete records from several child tables in the same query

I ve got a database model开发者_如何学Goize like this, One mother table let's call it table_mother, and severals child tables. Relation beetween table_mother and childs is like this:

All tables childs have a foreign key kind of name as the id of the mother table (id_table_mother) (relationship is 1->n as id_table_mother is uniq and tbale child can get several entries for id_table_mother)

I would like to delete all records in childs table wich are related no more with the mother table, for now i try something like this

           DELETE FROM tb_child_1,tb_child_2,tb_child_3
              WHERE 
tb_child_1.id_table_mother 
AND tb_child_2.id_table_mother 
AND tb_child_3.id_table_mother
              NOT IN (SELECT id_table_mother FROM tb_table_mother);

Thx

edit : this is how I ended for now

delete from tb_child_1 where id_mother not in (select id_mother from tb_mother_table);
delete from tb_child_2 where id_mother not in (select id_mother from tb_mother_table);

any "global" solution ? also my database is not innodb so I can 't go with foreigh keys and stuff

You have to build FOREIGN KEY Constraints to be performed on delete or update to know more about FOREIGN KEY Constraints visit http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Write 3 queries like this one -

DELETE
  tb_child_1
FROM
  tb_table_mother
LEFT JOIN
  tb_child_1
   ON tb_table_mother.id_table_mother = tb_child_1.id_table_mother
WHERE
   tb_child_1.id_table_mother IS NULL;