The SQL Server DELETE command is a DML (Data Manipulation Language) statement that is useful to remove one or more existing rows from a database table or view. The DELETE statement is very crucial for maintaining data integrity and the accuracy of the tables in a database.
Unlike the TRUNCATE command that removes all the records in a table, the SQL DELETE statement has more control over row removals. The main advantage is that DELETE allows you to specify the condition using the WHERE clause. So, you can delete a record based on a single condition or multiple conditions. Otherwise, one can remove multiple records or the entire dataset.
Generally, in an organization, after a while, the amount of data that we store in the database grows, and the records may be irrelevant or outdated to the current scenario. To remove these outdated records or clean the logs, you can use the SQL Server DELETE statement.
Unlike the DROP command, which removes the entire table from the database, the DELETE statement removes rows based on the condition (if any) and retains the table structure, constraints, etc.
TIP: Once you execute the SQL DELETE command, you can’t reverse the process and retrieve the data unless you use the TRANSACTIONS.
SQL DELETE Statement Syntax
The basic syntax of the SQL DELETE command, along with the WHERE clause to apply conditions on the SELECT statement is:
DELETE FROM [Table_Name] WHERE Condition
If you look at the syntax, we haven’t mentioned the list of columns in the SQL DELETE statement. Because the delete operation is performed on a row basis so we need the table name. Let me break the syntax for you.
- Table_Name: The name of the table from which you want to perform the deletion.
- WHERE Condition: It is an optional argument that provides the filters or conditions for removal. If the condition is TRUE, then only this Statement will delete the records from the given table.
With the help of the WHERE clause, you can delete a single word or multiple records based on the condition. However, if you omit the WHERE clause completely, the SQL DELETE statement removes all the records in a table.
Apart from the above simple syntax, there is a complex one that includes the TOP clause.
DELETE [TOP(Value) [PERCENT]]
FROM Table_Name
[WHERE condition]
TOP (Value) | PERCENT: Here, TOP clause is an optional argument, and if you use this, the SQL DELETE statement will remove the top few records based on the given integer or the percentage value from the table.
Let me show you some of the most common examples that you might face in real time. It includes removing all the records in the table, single row, multiple rows, single condition, and multiple conditions, etc. In this article, we use the table shown below to perform the different operation types of the DELETE operations.
Common Mistakes to Avoid
Before we dive deep into the SQL DELETE statement examples, let me show you the most common mistakes to avoid in real time.
- While writing the DELETE query, forgetting the WHERE clause and executing is a common and costly mistake.
- Using the DELETE command instead of the TRUNCATE statement while emptying the table.
- Forgetting to back up the tables before removing the records.
- Understand the referential integrity while performing DEETE operations on parent-child tables.
TIP: You can use the logical operators to combine multiple conditions inside the WHERE clause.
SQL DELETE a Single row
The SQL Server DELETE statement allows you to remove a single row or record from the given table. Imagine there is an Employee table, and you want to delete an employee who does not work in the organization. In such a case, you can use the WHERE condition to filter the table based on the employee ID. And then, remove that particular row.
To perform these operations, you must use the comparison operators like =, <=, >=, etc, to check the condition. To demonstrate the same, we will remove the EmpID = 2 from the employee table.
DELETE FROM [Employee]
WHERE [EmpID] = 2
It deletes the employee record whose ID value = 2, which is a single record. Suppose that none of the rows met the condition (no employee with ID = 2), the above statement does nothing.
Delete a Single Record Based on Multiple Conditions
The above SQL example is a simple one because we are using a single condition inside the WHERE clause to delete a single row from the table. However, there are some situations where we have to check multiple conditions to ensure we have deleted the exact matching row.
For instance, in the above table, we have three employees whose first name is John, and the worst part is that even the Education and Occupation of the two records are the same. In this case, we used the Logical AND operation to match both the first name and the last name to remove a particular record.
DELETE FROM [Employee]
WHERE [FirstName] = 'John' AND [LastName] = 'Ruiz'
NOTE: If you accidentally forgot the WHERE clause, then you will end up removing all the records from the Source.
SQL DELETE Multiple rows in a table
In some scenarios, there is a requirement to delete multiple records in a table. For instance, removing the underperforming or old department. Another case is removing the entire city or state from the business. Let’s say the following query deletes all the customers who belong to Los Angeles.
DELETE FROM Customers WHERE City = 'Los Angeles'
Let me give you a quick example to demonstrate the deletion of multiple columns. The below query will remove all the records in a table whose Education qualification is ‘Partial High School’.
DELETE FROM [Employee]
WHERE [Education] = 'Partial High School'
TIP: Use the @@ROWCOUNT to display the total number of records deleted by the statement.
SQL Delete Multiple Rows Based on Multiple Conditions
Similar to the above SQL DELETE statement or command example, we can use multiple conditions to create a more complex query to get the precise result set. In this example, we use the AND Operator in the WHERE Clause to check multiple conditions before it starts removing a record. The first condition tests whether the Occupation is Professional, and the AND operator is the conjunction. The next condition checks that the Yearly Income is less than 90000. If both conditions are TRUE, then that employee record will be deleted from the table.
DELETE FROM [Employee]
WHERE [Occupation] = 'Professional' AND [YearlyIncome] < 90000
Remove Multiple Rows using the BETWEEN operator
If you use the SQL DELETE statement along with the BETWEEN Operator and the logical AND operator, you can remove the rows in a range. For instance, removing the customer, order, or employees within a particular date range (from A to B date).
The below example removes the employees who joined between (their Hire Date) that fall inside the ‘2006-01-27 AND ‘2007-01-28’.
DELETE FROM [Employee]
WHERE [HireDate] BETWEEN '2006-01-27' AND '2007-01-28'
Delete Multiple Rows using the IN Operator in SQL
The above examples are useful to delete a range of records or the rows that match multiple or a single condition. However, there are some situations where we have to remove a particular set of individual records. For instance, remove different employee or student IDs.
The IN query below removes the employees whose ID values are 5, 10, 12, and 15.
DELETE FROM [Employee] WHERE EmpID IN (5, 10, 12, 15)
SQL DELETE all rows from the table
The SQL Server allows you to delete all the records or rows in a table without disturbing the table structure. It is very helpful when you want to remove all the old or unwanted data.
If you need to empty the table, you can achieve this by one of the three options: First, simply omit the WHERE clause.
DELETE FROM table_name
Second, use the WHERE clause with a condition that returns TRUE always (1 = 1).
DELETE FROM table_name WHERE 1 = 1
Third, use * (asterisk) to select all rows.
DELETE * FROM table_name
Although we can achieve the goal using any of the three options, always choose the first SQL DELETE statement option, which is straightforward to delete all the records present in a table. So, let me use the same to empty the above-mentioned table.
DELETE FROM Employee
As the above query empties the existing table, let me insert those 15 records from the backup to perform further operations.
SQL Server DELETE TOP Clause
The SQL Server allows you to use the TOP clause along with the DELETE statement to remove the first few records based on the number or the given percentage. For instance, if you want to delete the first 50 rows or 10% of the records from a table, use this TOP clause.
In this example, we remove the first record from the table whose yearly income is less than or equal to 5000. For this example, we use the DELETE command with the TOP clause and WHERE statement.
The WHERE clause will check for the records whose yearly income is less than or equal to 5000, and then the TOP Clause will select the First record. Finally, the DELETE command will remove that record.
DELETE TOP (1) FROM [Employee]
WHERE [YearlyIncome] <= 50000
If your goal is to remove the first 3 records irrespective of the condition, you can omit the WHERE clause.
DELETE TOP (3) FROM [Employee]
ORDER BY in Subquery
Remember, the DELETE TOP clause selects and removes the records in the order they were saved in the table. If you are particular about the records to delete, use the SQL ORDER BY clause inside the statement to sort them in ASC or DESC order based on the column. To demonstrate this, you must use the subquery.
The following query deletes the first five employee records from the table, ordered by their yearly income (least income).
DELETE FROM [Employee] WHERE [EmpID] IN (
SELECT TOP 5 EmpID FROM Employee
ORDER BY YearlyIncome ASC)
WITH CTE
Apart from the above subquery option, you can use the Common Table Expression (CTE) to get the result sorted in a specific manner. Next, perform the SQL DELETE operation on the CTE, instead of the table.
The below query creates a CTE that selects the top five employees whose yearly income is greater than 5000, arranged by their income in descending order. Next, the DELETE statement removes those records using CTE.
WITH Test
AS (
SELECT TOP 5 EmpID FROM Employee
WHERE [YearlyIncome] > 50000
ORDER BY YearlyIncome DESC )
DELETE FROM Test
SQL TRANSACTIONS in the DELETE Command
While performing data manipulation such as the DELETE operation, you must use the TRANSACTION concept. It helps to avoid mistakes and roll back the data to the original so that you don’t have to restore the data from the backup.
It means you start your query with BEGIN TRANSACTION, DELETE COMMAND, ROLLBACK (something went wrong), otherwise COMMIT the TRANSACTION. If you use the ROLLBACK TRANSACTION after the DELETE command, you can revert the table data to the start position or any saved point without restoring the data from backup.
BEGIN TRANSACTION
DELETE FROM Employee WHERE Occupation = 'Professional'
-- SELECT * FROM Employee
ROLLBACK TRANSACTION
-- SELECT * FROM Employee
Please try to uncomment the SELECT statements in the above query to see the TRANSACTION magic in real-time.
SQL DELETE Command with JOIN query
There are situations where we need to remove records from one table based on a condition tested in another table. In this situation, we use the JOIN concept to join the two or more tables and perform the deletion. The first image will show the date inside the Department table.
Let me remove the records from the employee table based on the Department name in another table. For this, we are using an Inner Join along with the SQL DELETE statement to join two tables. The following query removes all the records whose department name is Sr. Software Developer. Remember, the Department name comes from the Department Table.
DELETE e
FROM [Employee] AS e
INNER JOIN Department AS d ON e.[DeptID] = d.id
WHERE d.DepartmentName = 'Sr. Software Developer'
If you have the AdventureWorksDW database, you can use the following query to delete the sales records of the Male customers. Try to experiment on the DimGeography to remove sales records by Country, State, City, and Postal Code.
DELETE fact
FROM FactInternetSales fact
JOIN DimCustomer cus ON fact.CustomerKey = cus.CustomerKey
WHERE cus.Gender = 'M'
Use another Table to delete rows
In real-time, to maintain the common data, there are some situations where we have to check the data in one table (original) against the source table. If there are some duplicate or common records in the source and target, then we have to delete them from those tables. In such a situation, we can join both tables and perform the SQL Server DELETE operation on one table.
The following query will delete all the matching records in the Employee table whose id is the same as the Emp table.
DELETE Employee
FROM Employee
JOIN Emp ON Employee.EmpID = Emp.EmpID
The below query removes all the records in the Employee table whose records don’t match the Emp.
DELETE Employee
FROM Employee
JOIN Emp ON Employee.EmpID != Emp.EmpID
SQL DELETE Command Foreign Key Error Fix!
In general, the database tables are designed in such a way that they are interconnected using a Primary and Foreign key relationship. While creating the parent-child relationship, the Referential Integrity plays a major role in performing the SQL Server DELETE operation.
If you observe the default Adventure Works database, most of the foreign keys are marked with No Action Referential Integrity. So, when you try to delete a record from the main table where the child table has the dependent records, then the query will throw an error. It is very important to avoid the orphan records. For instance, if you try to delete a record from the Department table, it will throw an error because the Dept ID has references in the Employee table.
To deal with the orphan records, you can write a SQL DELETE statement to remove records in the main table and all the associated employees from the Employee table. To achieve the same, you have two options.
Option 1: CASCADE DELETE
If you have created the Foreign key with the CASCADE DELETE Referential Integrity, then it is a simple and straightforward technique. The following query will delete the Department name = Manager and all the employees’ records whose ID value is 6 (Manager).
DELETE FROM Department
WHERE DepartmentName = ‘Manager’
Option 2: Multiple DELETE Queries
If you haven’t created a Foreign key with CASCADE, then the above query will throw an error. To solve this, you must run two separate SQL DELETE statement queries to remove the records. The first query has to remove the employee rows whose department is Project Manager, and then write a second query to remove the Project Manager record from the Department table.
BEGIN TRANSACTION
DELETE FROM Employee
WHERE DeptID IN (
SELECT id FROM Department WHERE DepartmentName = 'Project Manager');
DELETE FROM Department WHERE id = 5;
COMMIT TRANSACTION;
You can also replace the IN operator query with the JOIN, and you can achieve the same result.
DELETE FROM Employee
JOIN Department ON Employee.DeptId = Department.id
WHERE DepartmentName = ' Project Manager';
DELETE FROM Department WHERE id = 5;
SQL DELETE FROM using a subquery
Writing a DELETE statement with a subquery is very helpful to remove the records in a table based on the result set obtained from the inner query. In our earlier TOP clause example, we have shown one of the subquery examples that you can refer to for a basic understanding.
In this SQL Server DELETE statement from a subquery result set example, we don’t know the department ID. But we want to delete the employees whose Dept name is Module Lead. Well, in such a case, we use the Subquery to SELECT records from the Department table whose Name is Manager. Next, let’s use the DELETE command and WHERE clause with the IN operator to delete multiple records written by the subquery.
DELETE FROM [Employee] WHERE [DeptID] IN
(
SELECT id FROM Department WHERE DepartmentName = 'Manager'
)
Eliminating all the records from the table whose department name is Manager. If you know the ID, you can try using the below query, but it is not the proper way.
DELETE FROM [Employee] WHERE [DeptID] = 3
The following query removes all the employees present in a table whose yearly income is less than the table average. The inner subquery finds and returns the average income of the whole table. The main DELETE command will remove the employees whose YearlyIncome is less than the average.
DELETE FROM Employee
WHERE YearlyIncome <
( SELECT AVG(YearlyIncome) FROM Employee)
SQL Server DELETE rows IF EXISTS
The WHERE clause cannot check the data within a single table. There are some situations where we must delete a record based on the second table’s condition. For instance, while removing a record from a table, it is always a good practice to check that the record exists.
The following query uses the EXISTS Operator to check the records in the Employee Table against the Department Table. If they exist and their Department name is CEO, then the DELETE statement will remove that record.
and the remaining data view is
DELETE sd FROM [Employee] as sd
WHERE EXISTS (
SELECT * FROM Department
WHERE sd.DeptID = Department.id
AND DepartmentName = 'CEO'
)
DELETE Duplicate Records
Assume there are some duplicate records in the customer table, and our job is to delete those records. In SQL Server, we can use the Subquery or CTE to find the duplicates and the DELETE statement to remove those records.
In general, the last inserted record might be the correct or required one. So, the below query will keep the highest ID value (assuming the latest) and delete the duplicates. If you want, you can replace MAX with the MIN function.
DELETE FROM customers
WHERE CustomerID NOT IN (
SELECT MAX(CustomerID) FROM customers
GROUP BY FirstName, LastName, DOB
)
The other option is using the CTE and ROW_NUMBER() rank function. In the following query, the ROW_NUMBER() function assigns a unique number to each row within a partitioned group. Here, it will assign numbers to each customer. Next, we use the DELEET statement to remove the records with row numbers greater than 1.
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY FirstName, LastName, DOB ORDER BY CustomerID) AS rank
FROM customer)
DELETE FROM CTE WHERE rank > 1
SQL Server DELETE and IS NULL Statement Example
The NULL values in a table represent the missing data. There are some cases where we have to get rid of those NULL values. In such a scenario, we use the DELETE statement along with the IS NULL operator.
As you know, there are many records in the AdventureWorksDW DimProduct table whose Product Description is empty or NULL. So, let me write a query to delete all the products whose Description is NULL.
DELETE FROM DimProduct
WHERE ProductDescription IS NULL
Using the OUTPUT Clause in SQL DELETE statement
The OUTPUT clause holds and displays the total number of records removed by the DELETE command. For instance, the following query deletes the employees whose sales are less than 1000. However, the OUTPUT clause will display the total records that are removed as the result set. Here, you can replace the * with the required column name if you need a few columns.
DELETE Employee
OUTPUT DELETED.*
WHERE Sales < 1000;
The above query is the simple version of using the OUTPUT clause to display the deleted result set. However, if you use the table variable, you can store the result set returned by the OUTPUT clause and perform further operations. The below query will create a table variable and uses the OUTPUT clause to save the removed rows by the DELETE statement. Next, the SELECT statement will display those records from the variable.
DECLARE @Variable TABLE (
[EmpID] [int] NOT NULL,
[FirstName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[Education] [nvarchar](255) NULL,
[Occupation] [nvarchar](255) NULL,
[YearlyIncome] [float] NULL,
[Sales] [float] NULL,
DepartmentName VARCHAR(50) NULL);
DELETE Employee
OUTPUT DELETED.EmpID, DELETED.FirstName, DELETED.LastName,
DELETED.Education,DELETED.Occupation,DELETED.YearlyIncome,
DELETED.Sales, d.DepartmentName
INTO @Variable
FROM Employee
JOIN Department as d
ON Emp.DeptID = d.id
WHERE d.DepartmentName = 'Software Developer';
SELECT * FROM @Variable
DELETE command using LIKE Operator
If we use the SQL LIKE operator in the WHERE clause, we can perform a wildcard search on records, and the DELETE command will remove the matching records. For instance, you can use the DELETE and LIKE combination to remove the customers belonging to the city name that starts with C, N, etc.
The following query will delete employees whose education contains the High keyword or whose Education ends with Degree word. I suggest you experiment with more wildcards to gain better knowledge.
DELETE FROM [Employee]
WHERE Education LIKE '%High%' OR Education LIKE '%Degree'
SQL Delete Stored Procedure
Let us see how to use this Statement inside a stored procedure. Here, we are removing records whose Occupation equals Clerical or whose yearly income is less than or equal to 60000.
IF OBJECT_ID ( 'sp_DeleteEmpRecords', 'P' ) IS NOT NULL
DROP PROCEDURE sp_DeleteEmpRecords;
GO
CREATE PROCEDURE sp_DeleteEmpRecords
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM [Employee]
WHERE Occupation = 'Clerical' OR
YearlyIncome <= 60000
END
GO
Messages
-------
Command(s) completed successfully.Let me use the EXEC Command to execute the stored procedure.
EXEC dbo.sp_DeleteEmpRecords
GO
Messages
-------
Command(s) completed successfully.Now, let us see whether the execution of the stored procedure cleared the rows in our table or not.
-- Select all records from a Table after executing the SP
SELECT * FROM [ProductSale]
SQL Server Delete Statement Using Management Studio
If you can access the Management Studio in SQL Server, there are multiple options to remove records from a database table.
Option A: Use Edit Top 200 Rows
Please right-click on the table and choose the Edit Top 200 rows option from the menu. Next, carefully select the required rows using the control or command key. And then, right-click on them to open the context menu. Either choose the Execute SQL command to further verify them, or select the DELETE option to permanently remove those rows from the table.
TIP: Use the SELECT statement before the DELETE command to check whether the records that you want to remove from the table are the ones displayed in the result set.
Option B: Using Script Table
Apart from the above two, you can use IntelliSense to write the DELETE statement. It is very advanced, and you get more control over the query. To do so, Right Click on the Table -> Select Script Table as -> Delete To -> New Query Editor Window. It will generate the statement for the selected table, and the Code is as shown below.
USE [Sample]
GO
DELETE FROM [dbo].[Employee]
WHERE <Search Conditions,,>
GO
First, let me backup the table to the original position. Next, let us add the below-shown Search Condition.
DELETE FROM [dbo].[Employee] WHERE EmpID = 1
SQL DELETE statement Best Practices
The following are some of the best practices for writing an effective SQL DELETE statement.
- User Permissions: The DELETE operation is very sensitive, so you must restrict unauthorized persons from deleting the tables.
- Data Backup: Before implementing the DELETE operation, always take a backup to avoid data loss. If you are working on a large dataset, the data loss will be huge and irreversible.
- WHERE clause: Always use the WHERE clause while writing the DELETE command. If you are sure that you want to delete the entire dataset, then only omit the WHERE clause.
- INDEX Columns: Make sure to use the Index columns in the WHERE clause to improve the query performance by many folds.
- SELECT: Before implementing the SQL DELETE command, use the SELECT statement to view the data. Or use SELECT COUNT(*) to see the total number of affected rows.
- TRANSACTIONS: It is always good practice to wrap the complete DELETE statement within the BEGIN and END TRANSACTION to ensure data integrity. If there is something wrong in the execution, you can ROLLBACK to the original.
- BATCH-WISE: While working on the large dataset, perform deletion on the small batches (TOP clause) to improve the query performance and avoid blocking.
- DELETE ALL ROWS: It is good practice to use the TRUNCATE statement to empty the whole table. Because it is faster and does not maintain the transaction logs of every single record deletion.
- OUTPUT clause: Use the OUTPUT clause to show the rows removed by the DELETE statement.
- Foreign Key Error: When there is a parent-child relation, deleting the records from the table will throw an error. Unless you created the foreign key with the CASCADE DELETE rule. Otherwise, first delete the records from the child table and then from the parent table.
- Disable Triggers and Constraints: While working with the large datasets, disable the foreign constraints. And also the dependent triggers will boost the performance. However, you should know why those triggers are in place and their importance. To disable trigger: ALTER TABLE Employee DISABLE TRIGGER ALL. Once the DELETE operation completes, write the above ALTER statement and replace the DISABLE keyword with ENABLE.
SQL DELETE Vs TRUNCATE vs DROP
The Microsoft SQL Server provides three commands: DELETE, TRUNCATE, and DROP. Because of their similarities, people are often confused by their operations, and here we show their differences.
- DELETE: It removes a single or multiple records in a table based on the condition given by the user.
- TRUNCATE: It removes all the records or rows in a table at one go and resets the identity column.
- DROP: It removes the complete table from the database.
The following table will illustrate the most common differences between the DELETE, TRUNCATE, and DROP commands in SQL Server.
| DELETE | TRUNCATE | DROP |
|---|---|---|
| DELETE is a Data Manipulation Language (DML) statement. | TRUNCATE is a Data Definition Language (DDL) statement. | DROP is a DDL statement. |
| It deletes one or more rows based on the condition. | Removes all records and keeps the table structure. | Deletes the table, structure, and constraints. |
| Use the DELETE command to remove a single row or multiple rows. | Use TRUNCATE to remove all records in the table and keep the table structure. | Use the DROP command to delete a table or database. |
| It allows the WHERE clause to filter the records to delete. | It does not support the WHERE clause. | No support for the WHERE clause. |
| It does not reset the identity column value. | It resets the identity column value. | Not applicable. |
| DELETE FROM orders WHERE OrderDate < ‘2020-01-01’ | TRUNCATE TABLE orders | DROP TABLE orders |
| It records each row deletion, so the transaction log is high. | It records only the data page, so less transaction space. | It records the entire table drop. So, it maintains the full log. |
| Performance is slower than others. | Faster than DELETE | Fastest among the three. |
| If there is a CASCADE DELETE referential integrity, you can delete the records with a SQL foreign key reference. | You cannot truncate the table with a foreign key reference. | You cannot drop the table when other tables have a reference to it. So, first, drop the dependency tables or relationships, and then apply the action. |
| It will fire the DELETE trigger on the table. | It does not fire any triggers on the table. | It won’t fire triggers on the table. |
| It keeps the triggers and constraints as it is. | It won’t delete the triggers and constraints. | It deletes the constraints and the triggers on the table |