如何删除不在的记录 [英] How to Delete Records NOT IN

问题描述

您好，我有以下 SQL 查询，它给了我 Scheme_Id，它同时存在于 ProjectSchemes 和 Schemes 表中.我想从 Schemes 表中删除所有没有记录到 ProjectSchemes 表的记录.我怎么能这样做?请帮忙.我正在使用 MSSQL

Hi I have the following SQL Query which gives me Scheme_Id which exist both in ProjectSchemes and Schemes table. I want to delete all records from Schemes table which have no record to ProjectSchemes table. How can I do so? Please help. I'm using MSSQL

select scheme_id from Schemes where Scheme_Id
in(select s.Scheme_Id from Projects p 
inner join ProjectSchemes ps on ps.Project_Id=p.Project_Id
inner join Schemes s on s.Scheme_Id=ps.Scheme_Id)

我正在尝试执行以下操作，但不起作用.不工作意味着没有记录受到影响，但当我检查我的 Schemes 表时，有太多的记录，他们的 scheme_id 在 ProjectSchemes 表中找不到

delete from Schemes where Scheme_Id
not in(select s.Scheme_Id from Projects p 
inner join ProjectSchemes ps on ps.Project_Id=p.Project_Id 
inner join Schemes s on s.Scheme_Id=ps.Scheme_Id)

推荐答案

我想从假设开始.

1. 您有一个链式数据模型:项目 --* 项目方案 --* 方案
2. 您的目标是只有有效的链，因此没有没有 Project 的 ProjectSchemes，没有没有 ProjectSchemes 的 Schemes.
3. NULL 不是您的 ID 之一的有效值.
4. 所有 ID 在其表中都是唯一的
5. 您没有使用数据库的参照完整性机制

因此，您的 SELECT 将在 Schemes 表中列出所有方案的 scheme_id.

As a result your SELECT would list the scheme_id for all Schemes in the Schemes table.

说到这里，你应该开始删除所有没有对应Project的ProjectSchemes.这些是 ID 为 NULL 或项目表中不存在的 ID 的 ProjectSchemes:

Said that, you should start to delete all ProjectSchemes without a corresponding Project. These are ProjectSchemes with an id of NULL or an id which does not exists in the Projects Table:

DELETE ProjectSchemes WHERE (Project_Id is NULL) OR 
(NOT EXISTS (SELECT * FROM Projects WHERE
             Projects.Project_Id = ProjectSchemes.Project_Id))

在没有项目的情况下删除 ProjectsSchemes 后，我们现在可能在 Schemes 表中有一些新的孤儿.接下来是删除所有 id 为 NULL 或在 ProjectsSchemes 表中不存在的 id 的方案:

After deleting the ProjectsSchemes without a Project we now may have some new orphans in the Schemes Table. The next thing is now to delete all Schemes which have an id of NULL or an id which does not exists in the ProjectsSchemes Table:

DELETE Schemes WHERE (Scheme_Id is NULL) OR 
(NOT EXISTS (SELECT * FROM ProjectSchemes WHERE
             ProjectSchemes.Scheme_Id = Schemes.Scheme_Id))

在不删除 ProjectSchemes 的情况下，仍有可能存在未连接到项目的方案.

There is still a chance to have schemes which are not connected to a project without deleting the ProjectSchemes.

这篇关于如何删除不在的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！