T-SQL Programming Part 8 – Removing Rows with the DELETE Statement in SQL Server
Greg Larsen explores how to remove rows from a SQL Server table using the DELETE statement.
In my last two articles I discuss using the INSERT and UPDATE statement. These two commands added new rows and modified existing rows. In this article I will explore how to remove rows from a table using the DELETE statement.
Syntax of the DELETE Statement
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. Here is the basic syntax of the DELETE statement.
( 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
For the complete syntax of the DELETE statement refer to Books Online.
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.
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:
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:
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:
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:
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:
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:
In order to use the RecordsToDelete table to delete specific records in my DemoDelete table I need to run the code below.
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:
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.
The following output displayed the SELECT statement in the above code snippet:
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.
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.
Multiple Ways to Delete Rows
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.