In SQL Server, DELETE TOP statement is used to delete the records from a table and limit the number of records deleted regarding a fixed value or percentage.
DELETE TOP (top_value) [ PERCENT ]
FROM [database_name].[dbo].[table_name]
[WHERE conditions];Parameter explanation
table: It specifies a table that you want to delete records from.
WHERE conditions: It is optional. The conditions that must be met for the records to be deleted.
TOP (top_value): It is used to delete the top number of rows in the result set based on top_value. For example, TOP(10) would delete the top 10 rows matching the delete criteria.
PERCENT: It is optional. It is used to delete the percentage of of top rows. For example, TOP(10) PERCENT would delete the top 10% of the records matching the delete criteria.
DELETE using TOP keyword
TOP keyword is used with DELETE statement to delete the specified top rows of a table.
Example:
We have a table “Employees”, having the following data.
Delete top 2 rows from the table “Employees” where salary is greater than or equal to 20000.
DELETE TOP(2)
FROM.[Employees]
WHERE salary >= 20000;
You can see that 2 rows are affected. Now see the output:
Output:
DELETE using TOP Percent Keyword
TOP percent keyword is used to delete the rows according to the percentage.
Example:
Delete TOP 25% rows from the table where salary is greater than 20000.
DELETE TOP(25) PERCENT
FROM .[Employees]
WHERE salary > 20000;
You can see that 3 rows are affected. 3 is exactly 25% of 12 which is the number of inserted rows.
Output:
Leave a Reply