CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
Scorpion
Scorpion

Reputation: 4585

SQL Server: WHERE NOT EXISTS is not working

I have a table Main1 which have 21 records and temp table @recordsToDel have 20 records. 20 Rows are identical in both tables but the following select query is not returning the missing record and delete is also not deleting it. Both table have similar columns.

Any suggestions please?

SELECT * FROM dbo.Main1
WHERE NOT EXISTS
(
    SELECT * FROM @recordsToDel
);

DELETE FROM dbo.Main1
WHERE NOT EXISTS
(
    SELECT * FROM @recordsToDel
);

Many Thanks

Upvotes: 1

Views: 14243

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

The LEFT JOIN includes all rows in the dbo.Main1 table in the results, whether or not there is a match on the r.Id column in the @recordsToDel table.

SELECT *
FROM dbo.Main1 m LEFT JOIN @recordsToDel r ON m.Id = r.Id
WHERE r.Id IS NULL

DELETE m
FROM dbo.Main1 m LEFT JOIN @recordsToDel r ON m.Id = r.Id
WHERE r.Id IS NULL

Upvotes: 1

James
James

Reputation: 680

You need a where statement in your subquery. SELECT * FROM @recordsToDel always returns rows, so they exist every time.

SELECT * 
FROM dbo.Main1 as m
WHERE NOT EXISTS
(
    SELECT * 
    FROM @recordsToDel as r
    where r.m_id = m.id
);

I saw in another comment you wanted to compare on different columns, if you wanted to select all the records from Main1 that don't exist in your sub table that have the same first name and last name you could make the where statement in the sub query be where r.firstname = m.firstname and r.lastname = m.lastname. But you can use whatever columns you want to achieve your results.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this 

 SELECT * FROM dbo.Main1
 EXCEPT
 SELECT * FROM @recordsToDel

Upvotes: 6

Related Questions