SQL Server Helper - Tip of the Day

The DELETE statement is a Data Manipulation Language (DML) statement that removes one or more rows from a table or view.

A simplified form of the DELETE statement syntax is as follows:

DELETE < table_or_view >
FROM < table_source >
WHERE < search_condition >

The < table_or_view > parameter names a table or view from which the rows are to be deleted. All rows in the < table_or_view > that meet the < search_condition > specified in the WHERE clause are deleted. If a WHERE clause is not specified, all the rows in the < table_or_view > are deleted.

The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from the < table_or_view >. Rows are not deleted from the tables named in the FROM clause but only from the table named in the < table_or_view >.

Here are a few examples of how to use the DELETE statement:

Using DELETE to Delete All Rows in a Table

DELETE FROM [dbo].[Transactions]

Using DELETE to Delete Certain Rows in a Table

DELETE FROM [dbo].[Account]
WHERE [IsExpired] = 1

Using DELETE to Delete Certain Number of Rows Only

DELETE TOP (1000)
FROM [dbo].[Transactions]
WHERE [TransactionDate] <= '2013/01/01'

Using DELETE to Delete Rows Based on Another Table

DELETE [dbo].[OrderDetails]
FROM [dbo].[OrderDetails] INNER JOIN [dbo].[OrderHeader]
                                  ON [dbo].[OrderDetails].[OrderID] = [dbo].[OrderHeader].[OrderID]
WHERE [dbo].[OrderHeader].[OrderNumber] = 'ABC123'

Using DELETE to Deleta Rows Based on Another Table Using Table Alias

DELETE [Det]
FROM [dbo].[OrderDetails] [Det] INNER JOIN [dbo].[OrderHeader] [Hdr]
                                        ON [Det].[OrderID] = [Hdr].[OrderID]
WHERE [Hdr].[OrderNumber] = 'ABC123'