SQL Delete TOP

SQL delete statement is used to delete records from table based on specific conditions which are specified in where clause.




Delete statement can also be used as Delete TOP, which also allows you to use TOP(n) with delete statement to deleting a first top n rows from table and TOP (n) PERCENT to deleting a top rows based on a n percentage of the total result set.

Syntax

DELETE TOP (top_value) [ PERCENT ] 
FROM table
[WHERE conditions];

TOP (top_value)

It deletes the top number of rows in the result set based on top_value.

For example, TOP(5) would delete the top 5 rows matching the delete criteria.

PERCENT

It is an optional, If PERCENT is specified, then the top rows are based on a top_value percentage of the total result set is deleted from table.

For example, TOP(50) PERCENT deletes the top 50% of the records from result set which matches the delete criteria.

Lets see how to use DELETE Statement with TOP and PERCENT.

First, we will create a new table and insert some dummy records into this table.

CREATE TABLE
TBL
(ID INT IDENTITY(1,1), Amount NUMERIC(9,2)
)

INSERT INTO TBL
(Amount)
VALUES
(100),(250),(300),(350),
(211),(302),(378),(400),
(410),(450),(500),(555),
(700),(750),(710),(711),
(789),(800),(500),(600)

 

Now we have a sample table named TBL in database as shown below.

Deleting top records using TOP with delete statement

Lets delete top 5 records from table those are having amount value greater than 500.

Before deleting records lets see how many records are there in table which have amount values greater than 500.

You can see, there are 8 records for which amount values are greater than 500.

Lets delete top 5 records those are having amount values are greater than 500.

DELETE TOP(5) FROM TBL
WHERE Amount >500

You can see, top 5 records matching the where condition have been deleted from table.

Lets check again for the records in table those have amount amount values greater than 500.

You can see now there are only 3 records left in table for amount value greater than 500, before deleting there were 8 records in table.

Deleting records using a TOP PERCENT with Delete Statement

As you have deleted some records from table in previous example, Lets truncate the table and re-insert records again.

TRUNCATE TABLE TBL;

INSERT INTO TBL
(Amount)
VALUES
(100),(250),(300),(350),
(211),(302),(378),(400),
(410),(450),(500),(555),
(700),(750),(710),(711),
(789),(800),(500),(600)

Lets delete the top 50% of records from result set which match the criteria where amount values are greater than 500.

Before deleting the rows lets do some observation and understand the behaviour of PECENT keyword with DELTE TOP.

Here you can see there are 8 records in the table which have amount values greater than 500.

So, if  you delete top 50% rows for which amount value is greater than 500, then 50% of total rows which matches the delete criteria would be (8 * 50)/100 = 4 rows.

That means DELETE(50) PERCENT would be deleting the top 4 rows from the result set where amount values are greater than 500.

Lets see what happens when you use top percent with delete statement to deleting the top 50 % of rows.

DELETE TOP(50) PERCENT FROM TBL
WHERE Amount >500

You can see, 4 rows have been deleted from table.

Lets see how many records are left for same condition.

SELECT * FROM TBL
WHERE Amount >500

You can see, only 4 records have been left in table where amount values are greater than 500.

That means 50% of records have been deleted from table. As there were total 8 records in table matching the delete criteria after deleting top 50 percent of records, only 4 records left in table.

Deleting TOP records with ORDER BY

As we have deleted some records in previous example, Lets truncate the table, and re-insert all records in table again.

TRUNCATE TABLE TBL;

INSERT INTO TBL
(Amount)
VALUES
(100),(250),(300),(350),
(211),(302),(378),(400),
(410),(450),(500),(555),
(700),(750),(710),(711),
(789),(800),(500),(600)

You can not use ORDER BY keyword directly with delete statement, Lets demonstrate this with the help of below example.

Assume that, you want to delete top 5 records from table which are having the highest amount value.

So to deleting the top 5 records from table by sorting amount in descending using ORDER BY keyword , would not be helpful.

You can see, using ORDER BY with DELETE gives an error.

So the solution for this, is you can use a sub query that will return Id of top 5 records which are having the highest amount, and delete statement deletes those ids returned by subquery.

DELETE FROM TBL WHERE ID
  IN(
      SELECT TOP(5) ID FROM TBL
        ORDER BY AMOUNT DESC
    )

You can see, 5 records that have highest amount is deleted from table.

Also Read..

Select TOP

SQL Delete

SQL Sub Query

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More