Sometimes, you may want to delete rows because they are no longer needed, or they were incorrectly added in the first place. The DELETE statement is used to remove rows from a SQL Server table. A single DELETE statement can remove a single row, or number of rows. In this article, I will explore how to remove rows from a table using the DELETE statement.
Here is the basic syntax of the DELETE statement:
DELETE
[ TOP ( expression ) [ PERCENT ] ]
[ FROM ] <object>
[ <OUTPUT Clause> ]
[ WHERE <search_condition>]
( expression ) - is a number or an expression that equates to a number used to limit the number of rows deleted.
<object> - is the name of an object in a database from which you want to delete records
<OUTPUT Clause> - identifies the column values of the deleted rows to be returned from the DELETE statement
<search_condition> - the condition used to identify the rows to be deleted
In order to demonstrate how to use the DELETE statement I will be creating a DemoDelete table. Here is the code I used to create and populate my DemoDelete table.
USE tempdb;
GO
CREATE TABLE DemoDelete (ID int,
DeleteDesc varchar(100));
GO
INSERT INTO DemoDelete VALUES
(1,'Thing One'),
(2,'Thing Two'),
(3, 'The Cat'),
(4, 'Sally'),
(5, 'The Brother'),
(6, 'The Mother'),
(7, 'The Fish');
Deleting a Single Row Using WHERE Constraint
In order to delete a single row from a table you need to identify that row with a WHERE constraint. Below is some code that deletes a single row from my DemoDelete table:
DELETE FROM DemoDelete
WHERE DeleteDesc = 'The Mother';
In this code I used the DeleteDesc column to constrain the records that I would be deleting. By specifying that the DeleteDesc column value had to be equal to the value "The Mother", only one record in my table got deleted, because only one row in my table had that value. Now if my table contained a number of rows that had a column value of "The Mother" then all the rows that contained that value would be deleted.
If you are unsure of the rows you are identifying to be deleted using the above example, and you want to make sure the rows you have targeted with the WHERE constraint are correct, then you can first run a SELECT statement. After you are confident that your SELECT statement is selecting the rows you want to delete you can then convert it to a DELETE statement.
Using the TOP Clause to Delete a Single Row
You can also use the TOP clause to delete a single row. Below is an example where I used the TOP clause to delete one row from my DemoDelete table:
DELETE TOP (1) FROM DemoDelete;
This statement deleted a random row from my DemoDelete table. It was random because SQL Server does not guarantee a sorted set will be returned where it can delete the top record of the ordered set. When I review the records left in my table I see I deleted the record that had an Id value of 1 and a DeleteDesc of "Thing One". Note if I change the TOP clause to another number like 3, then this statement would delete the number of rows equal to the value specified.
Deleting the TOP 1 Records from a Sorted Set
If you want to delete the first record from a sorted set you need to write your TSQL DELETE statement similar to the following code:
DELETE TOP (1) FROM DemoDelete
WHERE ID in
(SELECT TOP (1) ID FROM DemoDelete
ORDER BY ID DESC);
In the above code I create a subquery that returned a single ID value based on the descending sort order of ID column value in my DemoDelete table. I then used the WHERE constraint to only delete records that had that ID value. I also place a TOP (1) clause on my DELETE statement to only delete a single row should my DemoDelete table contain multiple records with the same ID value. If you are following along you can see the above code deleted the DemoDelete record that had an ID value of 7.
Since my DemoDelete table did not contain multiple records with the same ID value I could have also deleted the largest ID value row by running the following code:
DELETE FROM DemoDelete
WHERE ID in
(SELECT TOP (1) ID FROM DemoDelete
ORDER BY ID DESC);
When I run this code against my DemoDelete table it will delete ID value of 5.
Using Another Table to Identify the Rows to Delete and the OUTPUT Clause
There are times when you might what to delete the rows in a table based on values from another table. An example of where you might want to do this is to remove rows from your inventory table based on some sales data. To demo this first I will need to generate another table that contains key values for the rows I want to delete. Here is the code to create and populate my other table:
CREATE TABLE RecordsToDelete (
DeleteDesc varchar(100));
GO
INSERT INTO RecordsToDelete VALUES
('Thing Two'),
('Sally');
At this point after running all my different DELETE statements against my DemoDelete table there are only three rows left in my table. By selecting all the rows in my DemoDelete table I see that these three rows are left:
ID DeleteDesc
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
2 Thing Two
3 The Cat
4 Sally
In order to use the RecordsToDelete table to delete specific records in my DemoDelete table I need to run the code below:
DELETE FROM DemoDelete
OUTPUT DELETED.*
FROM DemoDelete INNER JOIN RecordsToDelete
on DemoDelete.DeleteDesc = RecordsToDelete.DeleteDesc;
This code joins the table DemoDelete and RecordsToDelete based on the DeleteDesc column. When the DeleteDesc matches between the two tables the matched rows within the DemoDelete table are deleted.
My delete statement above also contains the OUTPUT clause. The OUTPUT clause is used to return the column values of the deleted rows that are identified in the OUTPUT clause. In the code above I specified "DELETED.*". The "*" means to return all the columns values from the DELETED rows. When I ran this code the following rows were returned:
ID DeleteDesc
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
2 Thing Two
4 Sally
These returned rows could be used by your application for some purpose, like creating an audit trail.
Inserting OUTPUT Clause Data into a Table
There are times when you might retain the data created by the OUTPUT clause in a table instead of just returning the deleted row values to the application. To demonstrate running a DELETE statement that populates the row values being deleted into a table I will run the code below:
DECLARE @DeletedRows TABLE
(ID INT, DeleteDesc varchar(100));
DELETE FROM DemoDelete
OUTPUT DELETED.ID, DELETED.DeleteDesc
INTO @DeletedRows
WHERE DeleteDesc = 'The Cat';
SELECT * FROM @DeletedRows;
In this code sample I first created a table to contain my deleted rows. This table is a table variable name @DeletedRows. Next I ran my DELETE statement. This time my DELETE statement specified the deleted row output was to go into my table variable. That specification was made using the INTO clause of the DELETE statement.
The following output displayed the SELECT statement in the above code snippet:
ID DeleteDesc
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
3 The Cat
In both of my examples that used the OUTPUT clause of the DELETE statement I specified "DELETED.*" to denote outputting all the column values for the rows being deleted. I could have specified the actual column values I wanted to output. The code below is equivalent to the code above.
DECLARE @DeletedRows TABLE
(ID INT, DeleteDesc varchar(100));
DELETE FROM DemoDelete
OUTPUT DELETED.ID, DELETED.DeleteDesc
INTO @DeletedRows
WHERE DeleteDesc = 'The Cat';
SELECT * FROM @DeletedRows;
In this code you can see I specified "DELETED.ID, DELETED.DeleteDesc", instead of "DELETE.*". You can verify this code is equivalent by inserting the "The Cat" row back into the DemoDelete table and then running the code above.
As you can see there are multiple ways to delete rows from a SQL Server table. You can use the WHERE clause to identify specific criteria for the rows that need to be deleted. You can join a table to the table in which you are deleting rows to identify which rows to delete. You can even use the TOP clause to restrict the number of rows that will be deleted. The article should help you with developing your DELETE statement next time you have to remove some rows from a SQL Server table.
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
