SQLTeam.com | Weblogs | Forums

Delete vs select


#1

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!