Limiting Deleted Rows by Using TOP
You can use the TOP clause to limit the number of rows that are deleted in a DELETE statement. When a TOP (n) clause is used with DELETE, the delete operation is performed on a random selection of n number of rows.
For example, the following statement deletes 20 random rows from the PurchaseOrderDetail table that have due dates that are earlier than July 1, 2002.
USE AdventureWorks;
GO
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO
If you have to use TOP to delete rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement. The following query deletes the 10 rows of the PurchaseOrderDetail table that have the earliest due dates. To ensure that only 10 rows are deleted, the column specified in the subselect statement (PurchaseOrderID) is the primary key of the table. Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.
USE AdventureWorks;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
(SELECT TOP 10 PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderDetail
ORDER BY DueDate ASC);
GO
See Also
Concepts
Deleting Rows by Using DELETE
Deleting All Rows by Using TRUNCATE TABLE
Deleting Rows in Result Sets
Limiting Result Sets by Using TOP and PERCENT
Other Resources
TOP (Transact-SQL)
DELETE (Transact-SQL)
Deleting Data in a Table
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|