DELETE statement allows you to delete one or more rows from a table. Unlike UPDATE statement which updates specific columns in a row, DELETE statement deletes the entire row.
Syntax for DELETE statement is the simplest out of all other DML statements. At the very least, all you need is the DELETE keyword and the name of the table.
A query using the above syntax will delete all the rows in the specified table. Use of FROM keyword is optional in the above syntax.
To delete specific rows in a table, WHERE clause can be used in the DELETE statement. If a WHERE clause is specified, only rows meeting the WHERE condition in the table are deleted.
SYNTAX II:
DELETE FROM [Table Name]
WHERE [condition]
It is also very common to delete rows in a table based on data from another table. This can be done either using a subquery or by using JOIN operator.
SYNTAX III:
DELETE FROM Table1
FROM Table1 JOIN Table2 ON Table1.Column1 = Table2.Column1
WHERE [condition]
You can also limit the number of rows to delete by using TOP clause. TOP clause allows us to specify number of rows to delete.
For example, TOP (5) would delete top 5 arbitrary rows from the target table. You can also delete percentage of rows by adding PERCENT keyword in the TOP clause.
For example, TOP (5) PERCENT would delete top 5 percentage of arbitrary rows from the target table.
It’s very important to note here that DELETE statement using TOP clause does not follow any specific order when deleting rows.
SYNTAX IV:
DELETE TOP (n) PERCENT
FROM [Table Name]
Since TOP clause pulls out random rows from the target table, one way to delete rows in a specific order is by using a subquery having both TOP and ORDER BY clauses in it.
This syntax requires that the column in the SELECT clause of the sub query is a unique key or a primary key.
SYNTAX V:
DELETE FROM [Table Name]
WHERE [Column Name] IN (
SELECT TOP (n) [Column Name]
FROM [Table Name]
ORDER BY [Column Name] [ASC|DESC] )
Another way to delete rows using TOP clause in a specific order is by using Common Table Expression (CTE).
SYNTAX VI:
WITH [Expression Name]
AS
(
SELECT TOP (n) [Column Name]
FROM [Table Name]
ORDER BY [Column Name] [ASC|DESC]
)
DELETE FROM [Expression Name]
TRUNCATE statement is used to delete all the rows from a table.
SYNTAX:
TRUNCATE TABLE [Table Name]
If the objective is to delete only specific rows in a table, TRUNCATE statement cannot be used because it does not have a WHERE clause to filter rows in a table.
Similar to DELETE Statement, TRUNCATE statement does not affect the table structure, indexes or constraints.
But unlike DELETE Statement, TRUNCATE statement will reset the identity column to its default seed value.
If the objective is to delete all the rows in a table, it is preferable to use TRUNCATE statement instead of DELETE statement.
TRUNCATE statement executes faster and requires fewer resources on the server because unlike DELETE statement which records deletion of each and every row into the transaction log table,
TRUNCATE statement only records deallocations of data pages into the transaction log table. This also saves space in transaction log table.
TRUNCATE is also similar to DROP command. Both are DDL commands, but the difference is that DROP command removes the entire table and its contents whereas TRUNCATE only removes the contents.
If DROP command is used instead of TRUNCATE, you would need to recreate the table, indexes and all permissions associated to the table after executing the DROP command.
When using TRUNCATE statement, you need to make sure that
1. The target table is not referenced by any foreign keys. If there exists a foreign key reference to the table, then you must drop the index, truncate the table and then recreate the index.
2. There are no delete triggers on the table. If there exists any triggers which needs to be executed when deleting rows from a table,
then you should use DELETE statement instead of TRUNCATE statement because triggers are not executed when truncating a table.
The above query will delete all the records from the Employee table. Depending on the size of the table, this query could prove to be costly.
Instead of using DELETE to delete all the rows, TRUNCATE command should ideally be used for removing all the rows from a table.
Example 2:
DELETE FROM Employee
WHERE EmployeeID > 100
AND EmployeeID < 150
The above query deletes only those records where EmployeeID is in between 100 and 150.
Example 3:
DELETE
FROM Employee AS E
INNER JOIN Location AS L
ON E.EmployeeLocationID = L.LocationID
WHERE L.Name = 'Chicago'
The above query uses information in a secondary table to delete records in the target table. It deletes all employees located in Chicago from the employee table.
Example 4:
DELETE TOP (20)
FROM Employee
The above query randomly extracts 20 rows from the Employee table and deletes them.
Example 5:
DELETE TOP (20) PERCENT
FROM Employee
WHERE EmployeeID > 100
AND EmployeeID < 150
The above query deletes 20 percent of records from the employee table which satisfies the condition in the WHERE clause.
Example 6:
DELETE FROM Employee
WHERE EmployeeID IN (
SELECT TOP (10) EmployeeID
FROM Employee
ORDER BY EmployeeID DESC )
The above query contains a subquery which selects top 10 records from Employee table sorted by EmployeeID in descending order.
The outer query then deletes those records from the Employee table where EmployeeID matches the EmployeeID returned by the subquery.
Example 7:
WITH CTE
AS
(
SELECT TOP (10) *
FROM Employee
ORDER BY EmployeeID DESC
)
DELETE FROM CTE;
The above query uses Common Table Expression to delete specific number of rows in a specific order from Employee table.
The CTE defined in the above query contains two queries. The first query is a SELECT statement which extracts top 10 records from Employee table sorted by EmployeeID in descending order.
The second query with the DELETE statement simply deletes all the rows from the CTE.
The above query will delete all the records from the Employee table.