如何在同一个查询中删除多个子表中的记录

发布于 2024-11-09 16:27:55 字数 793 浏览 2 评论 0原文

我有一个像这样的数据库模型，一张母表（我们称之为 table_mother）和几张子表。 table_mother和childs之间的关系是这样的：

所有表childs都有一个外键类型名称作为母表的id（id_table_mother）（关系是1->n，因为id_table_mother是uniq并且tbale child可以获取id_table_mother的多个条目）

我想删除子表中与母表不再相关的所有记录，现在我尝试这样的事情

           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);

谢谢

编辑：这就是我现在结束

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);

任何“全局”解决方案的方式？而且我的数据库不是 innodb 所以我不能使用外键之类的东西

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

給妳壹絲溫柔 2024-11-16 16:27:55

您必须构建要在删除或更新时执行的外键约束，以了解有关外键约束的更多信息，请访问 http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

回复收藏 0 原文

生寂 2024-11-16 16:27:55

写 3 个这样的查询 -

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;

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;

回复收藏 0 原文

~没有更多了~