DELETE (Transact-SQL)
Removes one or more rows from a table or view.
Syntax
DELETE [ FROM ] { object_name | view_name }
[ WHERE ]
[ OPTION ( [ , ...n ] ) ]
Description
The DELETE statement removes rows from a table or view. You can delete individual rows, groups of rows that meet specific criteria, or all rows in a table.
When deleting from a view, the DELETE statement acts on the base table or tables that the view is defined on. The view must be updatable according to the rules for creating views.
The DELETE statement is the opposite of the INSERT statement. You can use DELETE to remove rows from a table.
DELETE is a DML (Data Manipulation Language) statement.
Arguments
- FROM: An optional keyword.
- object_name | view_name: The name of the table or view from which to delete rows.
- WHERE <search_condition>: Specifies that only rows matching the condition are deleted. If the
WHEREclause is omitted, all rows in the table or view are deleted. - OPTION ( <query_hint> [ , ...n ] ): Specifies that query hints are used.
Permissions
Requires DELETE permission on the object.
Examples
Example 1: Delete a single row
This example deletes the row from the SalesOrderDetail table where the SalesOrderID is 43659 and the SalesOrderDetailID is 1.
DELETE FROM SalesOrderDetail
WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;
Example 2: Delete multiple rows
This example deletes all rows from the SalesOrderDetail table where the OrderQty is less than 2.
DELETE FROM SalesOrderDetail
WHERE OrderQty < 2;
Example 3: Delete all rows from a table
This example deletes all rows from the SalesOrderDetail table. Use this with caution!
DELETE FROM SalesOrderDetail;
Example 4: Using a subquery in the WHERE clause
This example deletes rows from ProductInventory for products that have not been sold.
DELETE FROM Production.ProductInventory
WHERE ProductID IN (
SELECT ProductID
FROM Production.Product
WHERE NOT EXISTS (
SELECT 1
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = Production.Product.ProductID
)
);
Remarks
- A
DELETEstatement can be logged or minimally logged depending on the recovery model of the database. - If you want to delete all rows from a table, the fastest and most efficient method is to use
TRUNCATE TABLE. - Executing
DELETEwithout aWHEREclause will delete all rows in the table. - You can use triggers to execute other Transact-SQL statements or to run a stored procedure when rows are deleted.