将 T-SQL EXCEPT 与 DELETE 结合使用/优化查询 [英] Using T-SQL EXCEPT with DELETE / Optimizing a query

问题描述

以下代码从表中删除与非活动项目相关的任务记录.

The following code removes records of tasks related to inactive projects from the table.

delete from [Deliverables] where 
[Deliverables].[ProjectID] not in 
(
select 
[ProjectID] from [ActiveProjects]
)

我在某处读到使用 NOT IN 和返回大量值的子查询并不是最有效的做法，最好使用 EXCEPT 子句.

I've read somewhere that using NOT IN with subquery that returns a lot of values is not really the most efficient thing to do and it's better to use EXCEPT clause.

但是，当我尝试使用以下代码时，出现错误(关键字except"附近的语法不正确.)

However, when I try to use the following code, I get an error (Incorrect syntax near the keyword 'except'.)

delete from [Deliverables]
except
select * from [Deliverables], [ActiveProjects]
where [Deliverables].[ProjectID] = [ActiveProjects].[ProjectID]

如何将 EXCEPT 与 DELETE 一起使用?如果不能，有什么方法可以优化我的查询以加快执行速度?

How can I use EXCEPT with DELETE? If I can't, is there any way to optimize my query to execute faster?

推荐答案

你也可以试试 notexists 脚本看起来像:

You can try as well not exists and script would look like:

delete from [Deliverables] 
where not exists 
    (select 1 
        from [ActiveProjects]
        where [ActiveProjects].[ProjectID] = [Deliverables].[ProjectID])

如果[ActiveProjects]中有很多数据，应该是更好的解决方案，但都是数据相关的，所以请在使用前测试效率.

If there is a lot of data in [ActiveProjects] then it should be better solution, however it is all data dependent so please test efficiency before use.

这篇关于将 T-SQL EXCEPT 与 DELETE 结合使用/优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！