AND NOT EXISTS quesion
-
There are 38 records in the Table referred to below with a RecordID=2. I want to delete all of them except 4. The following code returns "0 rows affected". Why does it not delete all records with a RecordID = 2 other than the "Top 4"?
DELETE FROM tblTestResults
WHERE RecordID=2
AND NOT EXISTS (
SELECT TOP 4 *
FROM tblTestResults
WHERE RecordID=2
ORDER BY ResultID DESC
)
As a test, if I run "SELECT * FROM tblTestResults WHERE RecordId=2", the 38 records are returned, and if I run the "SELECT TOP 4... DESC" portion of the code, 4 of those 38 records are returned.
What am I doing wrong
Thanks in advance
-
Because what you are saying with this query is really "Delete all rows where RecordID=2 and where the subquery does not return anything". The subquery of course always returns 4 rows, so for every row it will evaluate to false, hence no rows deleted. If you have some primary key on the column (called pk in my example below) you could do it like this:
DELETE FROM tblTestResults
WHERE RecordID=2
AND pk NOT IN (
SELECT TOP 4 pk
FROM tblTestResults
WHERE RecordID=2
ORDER BY ResultID DESC
)
-
Thanks, that worked fine!!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply