To demonstrate how to delete top 'n' rows in SQL Server, let's suppose we have the following table:
+----+--------+
| id | name |
+----+--------+
| 1 | foo |
| 2 | bar |
| 3 | biz |
| 4 | baz |
| 5 | qux |
| 6 | quux |
| 7 | quuz |
| 8 | corge |
| 9 | grault |
| 10 | garply |
+----+--------+
CREATE TABLE customer
(
id INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
name VARCHAR(6) NOT NULL
);
INSERT INTO customer(name)
VALUES ('foo'), ('bar'), ('biz'), ('baz'), ('qux'), ('quux'), ('quuz'), ('corge'), ('grault'), ('garply');
#Deleting First 'n' Unordered Rows
If the ordering of the rows you wish to delete does not matter, then we can simply make use of DELETE TOP
like so:
-- SQL2005+
DELETE TOP (5) FROM customer;
For earlier versions of SQL Server that do not support DELETE TOP
, we can do the following:
DELETE FROM customer
WHERE id IN
(
SELECT TOP (5) id
FROM customer
);
Or, using the following altnerative SQL statement:
DELETE T FROM (
SELECT TOP (5) id
FROM customer
) T;
The queries above would result in:
+----+--------+
| id | name |
+----+--------+
| 6 | quux |
| 7 | quuz |
| 8 | corge |
| 9 | grault |
| 10 | garply |
+----+--------+
We can also achieve the same result by deleting based on a percentage using TOP PERCENT
. For example, to delete 50% of records, we can do the following:
DELETE TOP (50) PERCENT FROM customer;
#Deleting First 'n' Ordered Rows
To delete a set of ordered rows, we can use TOP
together with ORDER BY
in a subselect statement like so:
DELETE T FROM (
SELECT TOP (5) id
FROM customer
ORDER BY name
) T;
Or, alternatively:
DELETE FROM customer
WHERE id IN
(
SELECT TOP (5) id
FROM customer
ORDER BY name
);
Please note that deleting ordered rows can't be achieved by simply using DELETE TOP
because the rows referenced in the TOP
expression are not arranged in any order.
Please be aware of using a non-key column in the subselect statement as it may result in the deletion of more than the expected number of rows. This might be so because the result may contain duplicate values.
The queries above would result in the following:
+----+--------+
| id | name |
+----+--------+
| 5 | qux |
| 6 | quux |
| 7 | quuz |
| 9 | grault |
| 10 | garply |
+----+--------+
To delete based on percentage we can use TOP PERCENT
in the subselect statement. For example, to achieve the same result as in the examples above (i.e. delete 50% of records), we can do the following:
DELETE FROM customer
WHERE id IN
(
SELECT TOP (50) PERCENT id
FROM customer
ORDER BY name
);
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.