SQL Server: WHERE NOT EXISTS is not working

Question

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

Solution 1

Try this

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

Solution 2

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

Solution 3

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.

Comments

[Adir D]

You need to tell us how to determine that the rows are identical. Is there a column in dbo.Main1 that matches a column in @recordsToDel? WHERE NOT EXISTS (uncorrelated SELECT) will return either all rows in the parent or none, depending on whether there are 0 or more than 0 rows in the uncorrelated SELECT.

[paparazzo]

I would call that @recordsToKeep

[Scorpion]

Thanks a lot. Can I use the same way to delete the particular record?

[t-clausen.dk]

@MahmoudGamal no you cant, 'except' include a distinct that prevents it