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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 361 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.