如何从第二个表中删除“孤立的"记录 [英] How to delete 'orphaned' records from second table

问题描述

如果相应的记录不在另一个表中，我想从一个表中删除记录.

I would like to delete records from a table if the corresponding record is not present in another table.

即table1与table2具有一对多关系.我需要从table2中删除孤立的记录，其中table2.id在table1中不存在.

I.e. table1 has one-to-many relationship with table2. I need to delete orphaned records from table2 where table2.id is not present in table1.

我已经在Access中尝试过此操作

I have tried this in Access:

DELETE * FROM t2 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id is NULL

DELETE * FROM t2 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id is NULL

但是我收到"JOIN操作中的语法错误".我看不出有什么问题.

but I get "Syntax error in JOIN operation". I cannot see what is wrong.

推荐答案

在DELETE之后删除*..

我会这样解决的:

DELETE FROM t2
WHERE id not in (
   SELECT id from t1);

不确定通过联接删除是否可以正常工作.尽管您要删除连接中没有任何内容的第一部分中的所有行，但它必须是LEFT JOIN.另外，您正在将t2本身加入其中，可能只是错字..

Not sure if deleting with a join will work. It would need to be a LEFT JOINthough, as you want to delete all the rows in the first part of the join that is not joined with anything. Also, you are joining t2 with itself, guessing it's just a typo..

这篇关于如何从第二个表中删除“孤立的"记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！