I must be tired, but I can't see the obvious here. Consider this query:
DELETE -- SELECT *
FROM san.vm_capacity_fact
WHERE tk IN (
SELECT TOP 1000 tk
FROM san.vm_capacity_fact
ORDER BY tk DESC
)
IN SSMS, if I highlight from SELECT * to the end, I get 1000 rows.
However, if I run it from the DELETE (the commented SELECT is ignored of course), nothing is deleted.
Why?
FWIW, changing it to a JOIN has no effect. I still get no records deleted:
DELETE cf -- SELECT *
FROM san.vm_capacity_fact cf
INNER JOIN (
SELECT TOP 1000 tk
FROM san.vm_capacity_fact
ORDER BY tk DESC
) cf2 ON cf.tk = cf2.tk
Update:
I knew I was tired! I was relying on SSMS to give me the row count in the bottom-right of the results window for the delete. In reality, it only returns the count of rows selected. Since I selected no rows, the count was 0
I should add that I had SET NOCOUNT ON.
You get what you pay for!