CODEWITHSUNDEEP
sqlsql-server-2008-r2
PowerUser
PowerUser

Reputation: 11781

SQL Server: why does this Delete query not work?

I have 2 tables FinalList and RemoveTheseIDsList. They both have the same primary key of ID. I want to remove all rows from FinalList that are in RemoveTheseIDsList. So, I wrote this first query:

Delete from FinalList
    Where FinalList.ID not in (Select ID from RemoveTheseIDsList)

In theory, that query should have deleted every single row from FinalList. Instead, it deleted 0. I eventually settled on this clunky workaround (which does work):

Update FinalList set DeleteMe='Y'
    from FinalList FLL
    left join RemoveTheseIDsList REM on FLL.ID=REM.ID
    where REM.ID is null
Delete from FinalList where DeleteMe='Y'

My question is: why did that first query not work? Is there a quick fix that'll keep this query both functionable and small?

Upvotes: 1

Views: 2255

Answers (1)

GregM
GregM

Reputation: 2654

As you say : "I want to remove all records from FinalList that are in RemoveTheseIDsList"

So it should be : 

Delete from FinalList
Where FinalList.ID IN (Select ID from RemoveTheseIDsList)

But your update is like a not in so maybe try this : 

Delete from FinalList
Where FinalList.ID NOT IN (Select RemoveTheseIDsList.ID from RemoveTheseIDsList)

Upvotes: 4

Related Questions