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.
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.
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;
