Using T-SQL EXCEPT with DELETE / Optimizing a query

sql server except
delete all rows except one sql
sql delete all rows except top 100
how to remove in sql server
sql delete from select
sql except all
delete top 1 sql
delete multiple rows in sql

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]
)

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.

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]

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

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])

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.

Understanding the SQL EXCEPT statement with examples, In this article, you will learn how to use SQL EXCEPT with the help of involved in the corresponding SELECT queries should be either the  EXCEPT Returns any distinct values from the query left of the EXCEPT operator. Those values return as long the right query doesn't return those values as well. INTERSECT Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

delete d
from [Deliverables] as d
    inner join (
        select d2.[ProjectId] from [Deliverables] as d2
        EXCEPT
        select ap.[ProjectId] from [ActiveProjects] as ap
    } as todel on
        ((todel.[ProjectId] is NULL) and (d.[ProjectId] is NULL))
        or todel.[ProjectId] = d.[ProjectId]

A subtlety: The nice thing about EXCEPT is that it will equate NULL values, rather than deciding that they never match (as "=" does). In your trivial example (just looking at ids) this is unlikely to be of value, but if you are comparing a larger part of a table that allows nulls you'll have to account for this awkward "null matching" or miss a lot of rows.

SQL - EXCEPT Clause, How can we improve the update query performance in SQL Server? In this post I am going to use the term update (lower case) to apply to any operation that changes the state of the database (INSERT, UPDATE, DELETE, and MERGE in T-SQL). This is a common practice in the literature, and is used inside SQL Server too as we will see. The Three Basic Update Forms

Try it like this (modify to your needs by adding where-clauses and your column names etc)

delete from table1
  from table1 a
 inner join
      ( select your_column
          from table1
        except
        select your_column
          from table2
      ) b
    on a.your_column = b.your_column;

T-SQL commands performance comparison, the best way. We will start with creating two new tables, using the T-SQL script below: SQL EXCEPT command query results. Until this step  The query optimizer in SQL Server faces the same challenge as any relational query optimizer: It needs to find a good execution plan in the face of many options in a very short span of time. It is essentially playing a game of chess and evaluating move after move.

Query Hints (Transact-SQL), Because the SQL Server Query Optimizer typically selects the best execution developers and database administrators. Applies to: DELETE If more than one UNION hint is specified, the Query Optimizer selects the Prevents the query from using a nonclustered memory optimized columnstore index. SQL query optimization is being applied in order to minimize the possibility of your query being the system bottleneck. Hence, it is always good to know some good and simple ways to optimize your SQL query. In this article, you will get to see 15 simple and easy to applied SQL query optimization.

SQL Server, Here is another similar method using EXCEPT operator to find distinct results. In my earlier post on “SQL SERVER – Remove Duplicate Rows Using UNION Operator“, The following query will return all seven rows from the table Essentially my business secrets to optimize SQL Server performance. After that we will prepare the T-SQL queries that are used to pull the data that exists in Category_A table but not exists in Category_B table using four methods; NOT IN command, SQL NOT EXISTS command, LEFT JOIN command and finally EXCEPT command. This can be achieved using the T-SQL script below:

SQL WITH: Organize Complex Queries, The WITH clause adds named subqueries to SQL queries. Once created, a view has a name in the database schema so that other queries can use it like a table. 12: it optimized each with query and the main statement independent of each other. insert , update , delete ) within the body of with queries (​documentation). The EXCEPT operator is used to exclude like rows that are found in one query but not another. It returns rows that are unique to one result. It returns rows that are unique to one result. To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible data types.

Comments
  • this solution worked for me, it was significantly faster than using the NOT IN clause. Thank you!
  • This is a nice solution (and I upvoted), but doesn't really answer the question about the EXISTS part. EXISTS works with a SELECT statement. The only way to use it with a DELETE is if you also do a JOIN as shown in the other solutions (which is what I was trying to avoid). See also: stackoverflow.com/q/55423803/2949093