CODEWITHSUNDEEP
mysqlsql-deletecascading-deletes
krifur
krifur

Reputation: 890

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

I ve got a database modelize 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

Upvotes: 1

Views: 1160

Answers (2)

Devart
Devart

Reputation: 122032

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;

Upvotes: 0

Vineet1982
Vineet1982

Reputation: 7918

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

Upvotes: 1

Related Questions