In the previous chapter, we discussed the T-SQL’s Update statement. Now, in this chapter, we are going to discuss how we can use T-SQL’s DELETE command to delete records from a table in SQL Server. We will also discuss some best practices we need to follow while using the DELETE command in MS SQL Server.
What is DELETE command
DELETE command is used to delete/remove existing records from a SQL table. Depending on the need, we can delete all or a few selected records from a table. If required, we can delete the records in a table based on the records which exist in other tables. However, in this case, we need to join all the table(s) in the FROM clause of the DELETE statement. Let’s have a look at the syntax of the DELETE statement:
Syntax of DELETE command in SQL Server
To delete all the records in a table, below is the syntax:
DELETE [FROM] [<schema-name>.]<table-name>
Where,
DELETE is used to start a T-SQL’s DELETE command.
FROM keyword is optional and we can omit it if we want.
[<schema-name>.] is the name of the schema in which the table exists and is optional if the object exists in the default schema that is “dbo”.
<table-name> is the name of the table whose records need to be deleted.
Be cautious while executing the DELETE commands. Make sure that the required WHERE clause and JOIN conditions are in place before executing the DELETE command on a table. As a best practice, we can use transactions which allows us to rollback or commit the changes accordingly.
Example – DELETE all records of a table
Suppose, we want to delete all the employees from the dbo.Employee table which we have created in the previous chapter, we can use below DELETE statement:
DELETE FROM dbo.Employee
DELETE selected records
Below is the syntax to delete only those records which are satisfying a given condition:
DELETE FROM [<schema-name>.]<table-name>
WHERE <filter-condition>
Where,
WHERE is the WHERE clause which is used to specify a boolean condition.
<filter-condition> is the filter that needs to be applied on the table before the DELETE command gets executed on the table.
Example – DELETE selected records
Suppose, in the dbo.Employee table, we want to delete all the records of Employee 1 (whose Employee Id is 100). In order to do that we need to use below DELETE statement:
DELETE FROM dbo.Employee WHERE EmpId = 100
DELETE records based on multiple tables
Below is the syntax to DELETE records in a table based on other tables:
DELETE [<schema-name>.]<table-1>
FROM [<schema-name>.]<table-1>
JOIN [<schema-name>.]<table-2> ON <table-1>.<column-1> = <table-2>.<column-2>
WHERE [<filter-condition>]
Where,
FROM clause is the start of a FROM clause which is used to define the source table name and other JOIN conditions.
<table-1> is the name of the source table whose records need to be deleted.
JOIN <table-2> ON <table-1>.<column-1> = <table-2>.<column-2> is the JOIN condition which JOINS other tables to the source table from which records needs to be deleted. We can have multiple JOIN conditions as we do in SELECT statements.
It is recommended to convert the DELETE query into a SELECT query and verify the records first. Then, if it looks fine, we can execute the original DELETE statement by wrapping it inside a transaction.
Example – DELETE records based on other tables
Let’s create a dbo.Department and dbo.EmployeeDepartmentMapping tables with some dummy data so that we can JOIN these tables with the existing Employee table and delete the records accordingly. The create table and insert statement for the dbo.Employee table can be taken from the previous chapter. However, the below script includes the CREATE TABLE and INSERT commands for the dbo.Employee table along with dbo.Department and dbo.EmployeeDepartmentMapping tables. This script will drop and recreate all these tables in the database.
--Create Employee table with sample data
IF OBJECT_ID('dbo.Employee') IS NOT NULL
DROP TABLE dbo.Employee;
GO
CREATE TABLE dbo.Employee
(
EmpId INT NOT NULL,
EmpName VARCHAR(256) NOT NULL,
Address VARCHAR(512)
);
GO
INSERT INTO dbo.Employee
(EmpId, EmpName, Address)
VALUES (100, 'Employee 1', 'Delhi');
GO
--Insert more employees dynamically into Employee table
INSERT INTO dbo.Employee (EmpId, EmpName, Address)
VALUES
(200, 'Employee 2', 'Delhi'),
(300, 'Employee 3', 'Delhi');
GO
INSERT INTO dbo.Employee (EmpId, EmpName, Address)
SELECT TOP 10
(100 * (3 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)))) AS EmpId,
'Employee ' + CAST((3 + ROW_NUMBER() OVER(ORDER BY (SELECT 0))) AS VARCHAR(10)) AS EmpName,
'Delhi' AS Address
FROM sys.objects;
GO
--Create Department and EmployeeDepartmentMapping tables with sample data
IF OBJECT_ID('dbo.Department') IS NOT NULL
DROP TABLE dbo.Department;
GO
CREATE TABLE dbo.Department
(
DeptId INT NOT NULL,
DeptName VARCHAR(256)
);
GO
INSERT INTO dbo.Department
(DeptId, DeptName)
VALUES
(1, 'IT'),
(2, 'HR');
GO
IF OBJECT_ID('dbo.EmployeeDepartmentMapping') IS NOT NULL
DROP TABLE dbo.EmployeeDepartmentMapping;
GO
CREATE TABLE dbo.EmployeeDepartmentMapping
(
DeptId INT,
EmpId INT,
IsActive BIT
);
GO
INSERT INTO dbo.EmployeeDepartmentMapping
(DeptId, EmpId, IsActive)
VALUES
(1, 100, 1),
(1, 200, 1),
(2, 300, 1),
(2, 400, 1);
GO
Below is the screenshot of all three tables.
Now, suppose we want to delete all the employees working for Department 1 (which has DeptId 1) where employee and department mapping is currently active (IsActive = 1). Below is the DELETE query which joins the Employee table with Department and EmployeeDepartmentMapping tables, and deletes the matching records from the Employee table accordingly.
DELETE dbo.Employee FROM dbo.Employee e INNER JOIN dbo.EmployeeDepartmentMapping ed ON e.EmpId = ed.EmpId INNER JOIN dbo.Department d ON ed.DeptId = d.DeptId WHERE d.DeptId = 1 AND ed.IsActive = 1; GO
Thanks for the reading and please rate this post. If you like this post, please do share this on your social network.
