CODEWITHSUNDEEP
sqlsql-server
user4402742
user4402742

Reputation: 91

SQL Server delete rows by inner query

I'm running the following query on SQL Server: 

delete from thisTable where id not in (
select theID from thatTable )

without getting the effect i expect.

In thisTable, there are 2 rows and their id-s are 5 & 7.

In thatTable, 7 doesn't appear in the theID field of any row - but 5 does. However, the above query isn't deleting the rows of thisTable with id = 5. In fact, the above query isn't making any changes on thisTable no matter what.

what am i missing?

TIA.

Upvotes: 0

Views: 63

Answers (2)

jarlh
jarlh

Reputation: 44796

Any NULL's in the sub-query?

Try and re-write as

delete from thisTable 
where id not in (select theID 
                 from thatTable 
                 where theID is not null)

or

delete from thisTable 
where not exists (select * from thatTable
                  where thisTable.id = thatTable.theID)

Upvotes: 3

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Try this

DELETE FROM thistable WHERE ID NOT IN(
                            SELECT thistable.ID
                            FROM thistable INNER JOIN thatTable
                            ON thistable.id = thatTable.id)

SQL Fiddle

Upvotes: 0

Related Questions