March 2, 2016

How to delete the top 30 rows from a table using Sql Server

For those wondering why you can't do DELETE TOP (30) FROM table ORDER BY column, the answer is

 "The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE, or DELETE are not arranged in any order."

For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.
;WITH CTE AS
(
SELECT TOP 30 *
FROM [My_Table]
ORDER BY a1
)
DELETE FROM CTE

No comments:

Post a Comment