SQL 如何删除不在的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14953624/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Delete Records NOT IN
提问by Jobert Enamno
Hi I have the following SQL Query which gives me Scheme_Idwhich exist both in ProjectSchemesand Schemestable. I want to delete all records from Schemestable which have no record to ProjectSchemestable. How can I do so? Please help. I'm using MSSQL
嗨，我有以下 SQL 查询，它给了我Scheme_Id，它存在于ProjectSchemes和Schemes表中。我想从Schemes表中删除所有没有记录到ProjectSchemes表的记录。我怎么能这样做？请帮忙。我正在使用 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)
I'm trying to do the following but it's not working. Not working means no records affected but as I checked my Schemes table there are so many records that their scheme_id cannot be found on the ProjectSchemes table
我正在尝试执行以下操作，但它不起作用。不工作意味着没有记录受到影响，但当我检查我的 Schemes 表时，有太多的记录在 ProjectSchemes 表中找不到他们的 scheme_id
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)
采纳答案by S.Roeper
I would like to start with assumptions.
我想从假设开始。
- You have a chainlike data model: Projects --* ProjectSchemes --* Schemes
- Your target is to have only valid chains, so no ProjectSchemes without Project, no Schemes without ProjectSchemes.
- NULL is not a valid value for one of your ids.
- All ids are unique in their table
- You don't use referential integrity mechanisms of your database
- 您有一个链状数据模型：项目 --* ProjectSchemes --* Schemes
- 你的目标是只有有效的链，所以没有没有项目的项目方案，没有没有项目方案的方案。
- NULL 不是您的 ID 之一的有效值。
- 所有 id 在它们的表中都是唯一的
- 您不使用数据库的参照完整性机制
As a result your SELECT would list the scheme_id for all Schemes in the Schemes table.
因此，您的 SELECT 将在 Schemes 表中列出所有方案的 scheme_id。
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:
也就是说，你应该开始删除所有没有对应项目的 ProjectSchemes。这些是 ID 为 NULL 或项目表中不存在的 ID 的 ProjectSchemes：
DELETE ProjectSchemes WHERE (Project_Id is NULL) OR
(NOT EXISTS (SELECT * FROM Projects WHERE
Projects.Project_Id = ProjectSchemes.Project_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:
删除没有项目的 ProjectsSchemes 后，我们现在可能在 Schemes 表中有一些新的孤儿。接下来是删除所有具有 NULL id 或 ProjectsSchemes 表中不存在的 id 的 Schemes：
DELETE Schemes WHERE (Scheme_Id is NULL) OR
(NOT EXISTS (SELECT * FROM ProjectSchemes WHERE
ProjectSchemes.Scheme_Id = Schemes.Scheme_Id))
There is still a chance to have schemes which are not connected to a project without deleting the ProjectSchemes.
在不删除 ProjectSchemes 的情况下，仍有机会获得未连接到项目的方案。
回答by Rachcha
DELETE FROM schemes
WHERE scheme_id NOT IN (
SELECT DISTINCT scheme_id
FROM projectschemes
WHERE scheme_id IS NOT NULL
)
Or you can alternatively use
或者你也可以使用
DELETE
FROM schemes
WHERE NOT EXISTS (
SELECT 1
FROM projectschemes
WHERE projectschemes.scheme_id = schemes.ID
)
回答by Aleksandr Fedorenko
DELETE s
FROM Schemes s LEFT JOIN ProjectSchemes ps ON s.Scheme_Id=ps.Scheme_Id
WHERE ps.Scheme_Id IS NULL
But sounds like you need this
但听起来你需要这个
DELETE sp
FROM ProjectSchemes sp LEFT JOIN Schemes s ON sp.Scheme_Id=s.Scheme_Id
WHERE s.Scheme_Id IS NULL
