By: Jim Evans | Updated: 2022-07-29 | Comments (3) | Related: > TSQL
Problem
In this SQL tutorial, I demonstrate the use of the SQL DELETE statement to remove 1 row, a group of rows, or all the existing records from a Microsoft SQL Server table.
Before diving in too deep, understand that a SQL DELETE operation is dangerous. Once you run the DELETE, there is no recycle bin like in the Windows operating system where you can retrieve deleted records! To retrieve records, you must restore a database from a backup that occurred before the DELETE. To avoid having to restore the database, I included a best practice approach using the following basic transaction control statements in conjunction with the DELETE statement: BEGIN TRANSACTION, COMMIT, and ROLLBACK. These are more advanced topics, but putting these into practice as you learn to use DELETE can save your day!
Solution
SQL DELETE Statement
The syntax for a basic DELETE query includes the DELETE keyword and the table name with the schema name, which is sometimes optional depending on the database design. Also, it is important to consider including a WHERE clause. The WHERE clause is important to control the rows being deleted. Note: if a WHERE clause is not included, all rows of the entire table will be purged! Microsoft sample database, Adventureworks, will be used for this demonstration.
Delete Basic Syntax:
DELETE schema.TableName WHERE Col1 = 1; -- WHERE Condition
First - Setup the Delete Command Examples with Test Tables
First, I will set up a test table called dbo.MySalesPerson to use for my delete examples, as I do not want to mess up my sample database's integrity. Data will be loaded from existing tables to this table so we can issue DELETE statements throughout this demo.
Run the code below in the AdventureWorks database to set up the sample table for this demo.
USE [AdventureWorks]
GO
--A) CREATE TABLE statement to create our test table
CREATE TABLE [dbo].[MySalesPerson](
[BusinessEntityID] [int] NOT NULL,
[TerritoryID] [int] NULL,
[SalesQuota] [money] NULL,
[Bonus] [money] NULL,
[CommissionPct] [smallmoney] NULL,
[SalesYTD] [money] NULL,
[SalesLastYear] [money] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
);
GO
--B) Load Test Data from [Sales].[SalesPerson]
INSERT [dbo].[MySalesPerson]
SELECT *
FROM [Sales].[SalesPerson];
GO
--C) Insert Additional Rows for Non- Sales Persons.
INSERT [dbo].[MySalesPerson]
SELECT e.BusinessEntityID
,1 as [TerritoryID]
,NULL as [SalesQuota]
,0 as [Bonus]
,1 as [CommissionPct]
,0 as [SalesYTD]
,0 as [SalesLastYear]
,e.[rowguid]
,Getdate() as [ModifiedDate]
FROM [HumanResources].[Employee] e
WHERE JobTitle = 'Database Administrator';
--D) Explore the Test Data.
SELECT * from [dbo].[MySalesPerson];
GO
Example 1 – Basic DELETE Statement
The following query shows a basic DELETE statement with a WHERE clause to control what records should be deleted i.e., only one record. A check query can show the record with BusinessEntityID = 271 no longer exists.
USE [AdventureWorks] GO --1) Basic Delete Statement DELETE [dbo].[MySalesPerson] WHERE BusinessEntityID = 271; --Check Query SELECT * FROM [dbo].[MySalesPerson] WHERE BusinessEntityID = 271; GO
Example 2 – DELETE Command with Join Criteria
The following query uses a Join Query to delete rows that are in another table (WHERE JobTitle = 'Pacific Sales Manager').
--2) Delete With a Query
DELETE [dbo].[MySalesPerson]
FROM [dbo].[MySalesPerson] m
INNER JOIN [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Pacific Sales Manager';
--Check Query
SELECT JobTitle, m.*
FROM [dbo].[MySalesPerson] m
INNER JOIN [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Pacific Sales Manager';
GO
Example 3 – Remove All Rows from a Table
For this example, I will demonstrate two ways to clear all the data from a table. The first option will use a DELETE statement without a WHERE clause. The second option uses TRUNCATE, which requires ALTER TABLE permissions or higher. Also, I am including a transaction control statement to show the results of DELETE and TRUNCATE statements to undo the transaction, so I do not need to reset and reload the demo data. Please see the link at the end of this article to learn more about Transaction Control Statements.
It is important to mention that once a "BEGIN TRAN" statement is issued, you must run a COMMIT or ROLLBACK statement. If not, a perpetual opened transaction will be left, potentially causing contention or blocking in the database. Note: if you run either DELETE or TRUNCATE without the "BEGIN TRAN", you will need to rerun the "Load Test Data" previously provided in this article, because the data will be gone.
--3a) Option a: DELETE ALL Rows BEGIN TRAN; DELETE [dbo].[MySalesPerson]; --Check Query SELECT * FROM [dbo].[MySalesPerson]; --Now Rollback as I do not want to lose my data. ROLLBACK; --Show that no records where removed! SELECT * FROM [dbo].[MySalesPerson]; ---------------------------------------------- --3b) Option b: TRUNCATE Data --faster and uses fewer system and transaction log resources. per Microsoft Documents. BEGIN TRAN; TRUNCATE TABLE [dbo].[MySalesPerson]; --requires ALTER TABLE permissions or higher SELECT * FROM [dbo].[MySalesPerson]; --Now Rollback as I do not want to lose my data. ROLLBACK; SELECT * FROM [dbo].[MySalesPerson]; GO
Example 4 – Delete Foreign Key Error
Occasionally, the database design may have defined constraints that prevent deleting data. This example shows what happens if you attempt to delete records from table Person.BusinessEntity that is referenced by a Foreign Key. This is a way to protect data to prevent orphaned records in the database.
--4) Delete Error because of Foreign Key DELETE [Person].[BusinessEntity] WHERE BusinessEntityID = 1;
GO
Note that the result of the above query is an error message (below) notifying that this DELETE cannot happen. To correct this issue, you must first delete the referenced records from the table Person.BusinessEntityAddress.
Example 5 – Using Best Practices When Deleting Data
When deleting records, it is helpful to show the rows that will be affected. To do this, run SET NOCOUNT OFF. This command may be off by default. Next, perform a SELECT query that identifies the rows that could be deleted. Then you are ready for the DELETE. Again, Transaction Control Statements will be used to verify the delete results and to decide to COMMIT or ROLLBACK the DELETE transaction should the validation fail. This recommendation introduces the OUTPUT clause, which allows for an integrated way to see the deleted records to help with validation.
I recommend using the delete template below, which includes the pre-check query, transaction control statements, and the OUTPUT clause, which will return the deleted rows. Keep in mind these extra actions are included due to the dangers of a DELETE. Note that the OUTPUT clause does not work with TRUNCATE.
SET NOCOUNT OFF; --1) Check Query shows rows to be deleted SELECT * FROM schema.TableName WHERE Col1 = 1; --2) Run the DELETE in a Transaction BEGIN TRAN ; DELETE schema.TableName OUTPUT deleted.* FROM schema.TableName WHERE Col1 = 1; --3) Run ROLLBACK if the count does not match ROLLBACK; --Or Run COMMIT if row count matches COMMIT;
Option 5A – Best Practice Using OUTPUT Clause
An advantage of using the OUTPUT clause option is that the rows that have been deleted will be returned, allowing for positive verification.
----------------------------------------------------------------------
--5 OPTION A: Best Practice Output Query w/ ROLLBACK or COMMIT
----------------------------------------------------------------------
--Run SET NOCOUNT OFF to show row count results.
SET NOCOUNT OFF;
--1) Check Query shows rows to be deleted
SELECT m.*
FROM [dbo].[MySalesPerson] m
INNER Join [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
--2) Run the DELETE in a Transaction
BEGIN TRAN;
DELETE [dbo].[MySalesPerson]
OUTPUT Deleted.*
FROM [dbo].[MySalesPerson] m
INNER Join [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator'
--Stop Here to Validate.
--Now either Commit or Rollback the transaction based on the Check Query results!
COMMIT;
--OR
ROLLBACK;
Option 5B – Best Practice Using Check Queries
This option may be preferred as the concept to validate the results is the same, but it uses queries instead of the OUTPUT clause.
----------------------------------------------------------------------
--5 Option B: Best Practice Check Table w/ ROLLBACK or COMMIT
----------------------------------------------------------------------
--Run SET NOCOUNT OFF to show row count results.
SET NOCOUNT OFF;
--1) Check Query shows rows to be deleted
SELECT m.*
FROM [dbo].[MySalesPerson] m
INNER Join [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
--2) Run the DELETE in a Transaction
BEGIN TRAN;
DELETE [dbo].[MySalesPerson]
FROM [dbo].[MySalesPerson] m
INNER Join [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator'
--3) Again Run the Check Query to verify Results
SELECT m.*
FROM [dbo].[MySalesPerson] m
INNER Join [HumanResources].[Employee] e
ON e.BusinessEntityID = m.BusinessEntityID
WHERE JobTitle = 'Database Administrator';
--RUN THE Query Above Up to Here to review the output of the Check Query.
--Now either Commit or Rollback the transaction based on the Check Query results!
COMMIT;
--OR
ROLLBACK;
GO
Wrap Up
Through this demo, you have an opportunity to see how to DELETE data from a SQL table and to understand the dangers of unintended deletes and the importance to validate the delete results. See the links below to learn more about basic SQL statements and new topics presented throughout this article.
Next Steps
- Learn more about SQL Transaction Control Statements
- Read about the Basic SQL INSERT Statement with Examples
- Learn How to Write a SQL SELECT Statement
- Visit Microsoft Docs to learn more about TRUNCATE
- Get more on SQL SELECT INTO Examples
- Read about SQL JOIN Types with Examples
- Check out the following tips:
About the author
Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-07-29
