删除表中所有在另一个表中没有引用的记录 [英] Delete all records in table which have no reference in another table

问题描述

我有一个名为 Document 的表格.

I have a table which is called Document.

文档:

id int
docuid int
doc blob

然后我有两个引用表

AppRequiredDocuments:

id int
appid int
docid int -> references document -> id

应用文档:

id int
appid int
docid int -> references document -> id

由于非常旧的迁移，文档表中的孤立项目必须引用其他表中的项目.如何仅删除AppDocuments 或AppRequriedDocuments 中未引用的文档表中的文档?

I have due to an very old migration orphaned items in the document table which have to references in the other tables. How can I delete only the documents in the document table which are not referenced in AppDocuments or AppRequriedDocuments?

推荐答案

您可以使用 NOT EXISTS 来查找和删除这些项目:

You can use NOT EXISTS to find and delete those items:

delete from document d
where not exists (select 1 from AppRequiredDocuments a where a.docid = d.id);
and not exists (select 1 from AppDocuments a where a.docid = d.id);

这篇关于删除表中所有在另一个表中没有引用的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！