This article is part of Greg Larsen's continuing series on Learning T-SQL. To see all the items in the series, click here.
Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article “Updating SQL Server Data” I discussed using the UPDATE statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating how to use the DELETE statement to remove rows from a SQL Server Table.
Syntax of the Basic DELETE statement
Deleting data seems like a simple concept so you would think the syntax for a DELETE statement would be super simple. In some respects that is true, but there are many ways and aspects of how the DELETE statement can be used. In this article I will be discussing only the basic DELETE statement.
The syntax for basic DELETE statement can be found in Figure 1:
DELETE
[ TOP [] [ PERCENT ] ]
[ FROM ]
[ WHERE ]
Figure 1: Basic syntax of the DELETE statement
Where:
expression– Identifies the number or percentage of rows to be delete. When only theTOPkeyword is identified the expression identifies the number of rows to be deleted. It the keywordPERCENTis also included then the expression identifies the percentage of rows to be delete.object– Identifies the table or view from which rows will be deleted.search_condition– Identifies the criteria for which a row must meet in order to be deleted. Thesearch_conditionis optional. When it is excluded from aDELETEstatement, then all the rows in the object will be deleted.
For the complete syntax of the DELETE statement refer to the Microsoft Documentation which can be found here.
To better understand the syntax of the simple DELETE statement and how to use it, several examples are provided below. But before these examples can be run a couple sample tables need to be created.
Sample Data
Listing 1 contains a script to create two sample tables in the `DELETE`4 database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
USE tempdb;
GO
CREATE TABLE dbo.LoginTracking [
ID INT IDENTITY[1,1],
LoginName varchar[100],
LoginDataTime datetime,
LogoffDateTime datetime];
GO
INSERT INTO dbo.LoginTracking VALUES
['Scott','2022-07-11 08:41:31','2022-07-11 11:45:50'],
['Sally','2022-07-11 08:55:27','2022-07-11 11:59:59'],
['Dick','2022-07-11 09:05:17','2022-07-11 16:15:37'],
['Dick','2022-07-12 08:05:11','2022-07-12 15:50:31'],
['Scott','2022-07-12 08:12:27','2022-07-12 16:11:22'],
['Sally','2022-07-12 09:20:06','2022-07-12 16:45:11'],
['Dick','2022-07-13 08:10:13','2022-07-13 15:59:45'],
['Scott','2022-07-13 08:12:37','2022-07-13 16:21:38'],
['Sally','2022-07-13 09:07:05','2022-07-13 16:55:17'];
GO
CREATE TABLE dbo.LoginsToRemove[
LoginName varchar[100]];
GO
INSERT INTO dbo.LoginsToRemove VALUES ['Sally'];
GO
Listing 1: Script to create sample tables.
The first table created in Listing 1 is the `DELETE`5 table. This table is the table from which rows will be deleted. The second table created is `DELETE`6. This table is a staging table that contains a single column named `DELETE`7. This table will be used to show how to delete rows using rows in another table.
If you would like to follow along and run the code in this article you can use Listing 1 to create the two sample tables in the `DELETE`4 database on your instance of SQL Server.
Deleting a Single Row
To delete a single row, the DELETE command needs to identify the specific row that needs to be deleted. This is done by including a `DELETE`0 constraint. The `DELETE`0 constraint needs to uniquely identify the specific row to delete. The code in Listing 2 will delete the `DELETE`2 record that have a `DELETE`7 “Scott” and an ID value of 1.
USE tempDB;
GO
DELETE FROM dbo.LoginTracking
WHERE LoginName \= 'Scott' and ID \= 1;
GO
Listing 2: Deleting a single row
In the DELETE`2 table there are multiple rows that have a `DELETE`7 of “Scott”. Therefore the `DELETE`6 column value of “`DELETE`7” was also included in the `search_condition to uniquely identify which single row was to be deleted.
Only the ID column alone could have been used to identify the single record to be deleted. However, in many cases having extra filter conditions can be useful. For example, if the row where `DELETE`9 has a different `DELETE`7, it would not be deleted.
Note: When you are expecting a certain number of rows to be deleted, it is a good idea to check the value in `DELETE`1 to make sure.
Deleting Multiple Rows
A single DELETE statement can also be used to delete multiple rows. To delete multiple rows the `DELETE`0 clause needs to identify each row in the table or view that needs to be deleted. The code in Listing 3 identifies two `DELETE`2 rows to delete by identifying the `DELETE`6 column values for the rows to be deleted.
USE tempDB;
GO
DELETE FROM dbo.LoginTracking
WHERE ID \= 2 or ID\=3;
GO
Listing 3: Deleting multiple rows with a single DELETE statement
By specify a `DELETE`0 clause, that identifies multiple rows the code in Listing 2 will delete multiple rows in the `DELETE`2 table when this code is executed.
Using the TOP Clause to Delete Rows of Data
When the TOP clause is included with a DELETE statement, it identifies the number of random rows that will be deleted from the table or view being referenced. There are two different formats for using the TOP clause. `DELETE`3 identifies the number of rows to be delete.
The code in Listing 4 shows how to use the TOP clause to delete one random row from the `DELETE`2 table.
USE tempdb;
GO
DELETE TOP[1] FROM dbo.LoginTracking;
GO
Listing 4: Deleting one row using the TOP clause
The reason the top clause deletes random row is because SQL Server does not guarantee the order rows will be returned, without an `DELETE`7 clause.
If you need to delete rows in a specific order, it is best to use a subquery that uses TOP and an order by, as shown in Listing 5.
USE tempdb;
GO
DELETE TOP [2] FROM dbo.LoginTracking
WHERE ID IN
[SELECT TOP[2] ID FROM dbo.LoginTracking ORDER BY ID DESC];
GO
Listing 5: Deleting the last 2 rows based in `DELETE`6
Executing the code in Listing 5 deletes the last two rows in the DELETE`2 table, based on the descending order of the ID column. The code accomplished this by using a subquery in the `DELETE`0 constraint. The subquery uses the `TOP clause to identify the two `DELETE`6 values that will be deleted based on descending sort order. This list of `DELETE`6 values is then used as the `DELETE`0 filter condition to identify the two rows to be delete.
Note: in this case, the expression`5 in the `DELETE is technically redundant. But if the DELETE`6 column didn’t contain unique values, and the `TOP clause would need to be included to make sure only two rows would have been deleted,.
The TOP clause in the prior two examples identified a specific number of rows to be deleted. The TOP clause also supports identifying a percentage of rows to delete. Before showing an example of deleting a percentage of rows using the TOP clause let’s first review the details of the rows still in the `DELETE`2 table, by running the code in the Listing 6.
USE tempdb;
GO
SELECT * FROM dbo.LoginTracking;
Listing 6: Displaying the rows currently in the `DELETE`5 table
When the code in Listing 6 is run the results in Report 1 are displayed.
![Text Description automatically generated][////i0.wp.com/www.red-gate.com/simple-talk/wp-content/uploads/2022/11/text-description-automatically-generated.png]
Report 1: Current rows in the `DELETE`2 table.
Currently there are 5 rows in the `DELETE`2 table.
To delete a percentage of the rows in a table the TOP clause is use along with the PERCENT keyword. The expression provided provide with the TOP clause identifies the percentage of rows to delete. The code in Listing 7 specifies 41 percent of the rows in the `DELETE`2 table should be deleted.
USE tempdb;
GO
DELETE TOP[41] PERCENT FROM dbo.LoginTracking;
GO
SELECT * FROM dbo.LoginTracking;
GO
Listing 7: Deleting 41 percent of the rows using the TOP clause.
When Listing 4 is executed the output in Report 2 is displayed.
![Graphical user interface, text Description automatically generated][////i0.wp.com/www.red-gate.com/simple-talk/wp-content/uploads/2022/11/graphical-user-interface-text-description-automa-2.png]
Report 2: Number left in `DELETE`2 after the code in Listing 7 was run
By reviewing Report 2 and comparing it with Report 1 results, you can see 3 rows were deleted, which means 60 percent of the rows were deleted. Why 60 percent? The reason 60 percent of the rows were deleted is because SQL Server needs to delete a percentage of rows that equates to a whole number. Since 41 percent of 5 rows would have been 2.05 of the rows in the DELETE`2 tracking table, SQL server rounded the row count up to 3. 3 equates to 60 percent of the rows when it performed the `DELETE statement.
Using a Table to Identify the Row to Delete
There are times when you might need to identify rows to delete based on a table. To show how to delete rows based on rows in a table, the `PERCENT`4 table was created in Listing 1. The `PERCENT`4 table could be considered a staging table, which identifies the logins that need to be deleted. The code in Listing 8 uses this table to delete rows from the `DELETE`2 table.
USE tempdb;
GO
DELETE FROM dbo.LoginTracking
FROM dbo.LoginTracking JOIN dbo.LoginsToRemove
ON LoginTracking.LoginName \= LoginsToRemove.LoginName
GO
Listing 8: Using a table to identify rows to be deleted from a table
The code in Listing 8 joined the `PERCENT`4 table with the `DELETE`2 table on the `DELETE`7 column from each table. This join operation only finds those rows in the `DELETE`2 table which have a matching `DELETE`7 in the `PERCENT`4 table. In this example all the records that had “`object`3” as a `DELETE`7 got deleted from the `DELETE`2 table.
Deleting all Rows from a Table
The DELETE statement can be used to remove all the rows in a table. To accomplish this a DELETE Statement without a `DELETE`0 constraint can be executed, as in Listing 9.
USE tempdb;
GO
DELETE FROM dbo.LoginTracking;
GO
Listing 9: Deleting all the rows in a table.
Even though the DELETE statement can delete all the rows in a table, as done in Listing 9, this is not the most efficient way to delete all the rows in a table. The DELETE statement performs a row-by-row operation to delete all the rows. Every time a row is deleted information needs to be written to the transaction log file, based on the database recovery model option. If there are a lot of rows in a table, it could take a long time and use a lot of resources to delete all the rows using a DELETE operation.
An alternative method to remove all rows in a table
A more efficient, though more limited method to delete all rows is to use the `search_condition`2 statement as shown in Listing 10.
USE tempDB;
GO
TRUNCATE TABLE dbo.LoginTracking;
GO
Listing 10: Deleting all the rows in a table using a `search_condition`2 statement
When a search_condition`2 statement is used, less information is logged to the transaction log file, as well as has some other significate differences. One of those differences is when a `DELETE statement is used, if all the rows are deleted from a physical `search_condition`6 page the page is left empty in the database, which wastes valuable disk space. Additionally, a `search_condition`2 option requires less locks to remove all the rows.
Another important difference is how these two different delete methods affect the identity column’s seed value. When a search_condition`2 statement is executed the seed value for the identity column is set back to the seed value of the table. Whereas the `DELETE statement retains the last identity column value inserted. This means when a new row is inserted, after all the rows were deleted, using a DELETE statement, that the next identify value will not start at the seed value for the table. Instead, the new row’s identity value will be the determined based off the last identity value inserted, and the increment value for the identity column. These differences need to be kept in mind when deleting rows using the DELETE statement during testing cycles.
There are two major limitations to consider. First, the table being truncated cannot be referenced in a DELETE`02 constraint. The second is security. To be able to execute a `DELETE statement that references a table, you need DELETE permissions. To execute `search_condition`2 you need `DELETE`06 rights, which gives the user the ability to change the table’s structure. For more details on `search_condition`2, check the Microsoft documentation here.
Deleting data using a view
Deleting from a view is the same as deleting from a table, with one caveat. The delete can only affect one table. This essentially means that the DELETE`08 clause of the view can only reference one table to be the target of a `DELETE statement. You could have conditions [like subqueries] that reference other objects, but no joins. For example, in listing 11, consider the two view objects.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE VIEW dbo.v_LoginTracking
AS
SELECT ID,
LoginName,
LoginDataTime,
LogoffDateTime
FROM dbo.LoginTracking;
GO
CREATE VIEW dbo.v_LoginTracking2
AS
SELECT LoginTracking.ID,
LoginTracking.LoginName,
LoginTracking.LoginDataTime,
LoginTracking.LogoffDateTime
FROM dbo.LoginTracking JOIN dbo.LoginsToRemove
ON LoginTracking.LoginName \= LoginsToRemove.LoginName
GO
Listing 11: View objects to demonstrate how deleting from a view works
`DELETE`10 a DELETE statement in the first view: `DELETE`11, will work. But attempt to delete rows using the second view: `DELETE`11, and you will get the following error.
`DELETE`13
Deleting Rows from a SQL Server table
The DELETE statement is used to delete rows in a SQL Server table or view. By using the DELETE statement, you can delete one or more rows from an object. If all the rows in a table need to be deleted, a DELETE statement is less efficient then using the `search_condition`2 statement, but has fewer limitations and doesn’t reset the identity value information.
Knowing how to use the basic DELETE statement, and when not to use it is important concept for every TSQL programmer to understand.
How to delete top 2 rows in SQL?
DELETE using TOP keywordTOP keyword is used with DELETE statement to delete the specified top rows of a table. Example: We have a table "Employees", having the following data. Delete top 2 rows from the table "Employees" where salary is greater than or equal to 20000.
How do I DELETE top 5 rows in MySQL?
To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.
How to delete n number of rows in SQL?
If you wanted to delete a number of rows within a range, you can use the AND operator with the BETWEEN operator. DELETE FROM table_name WHERE column_name BETWEEN value 1 AND value 2; Another way to delete multiple rows is to use the IN operator.
How to delete last n rows in SQL?
The Syntax for Using the SQL Delete CommandWHERE [condition]; The table from which we want to delete rows is specified in the table_name parameter of the DELETE FROM statement. There is an optional WHERE clause in which we can specify the condition according to which the rows should get deleted.