DELETE (Transact-SQL)
Removes one or more rows from a table or view.
Syntax
DELETE
[ FROM ]
[ .. ]
[ ]
[ ]
[ WHERE ]
[ OPTION ( [ ,...n ] ) ]
-- For SQL Server 2008 and later, you can also use the OUTPUT clause.
[ OUTPUT INTO ]
FROM
[ .. ] [ ]
[ ,...n ]
WHERE
[ NOT ]
Arguments
- FROM: An optional keyword that can be used in front of the table or view name to be deleted from.
- <owner_name>.<user_name>.<table_or_view_name>: The name of the table or view from which to delete rows. If the table or view is not qualified with an owner name, the default schema is the user's default schema. If the database context is not that of the current database, the table must be qualified with the database name.
- <alias>: An optional alias for the table or view name.
- <FROM_clause>: Specifies additional tables or views to use in the DELETE statement. This allows you to delete rows from a table based on criteria defined in other tables.
- WHERE <search_condition>: Specifies the criteria for the rows to be deleted. If the WHERE clause is omitted, all rows in the table are deleted.
- OPTION ( <query_hint> [ ,...n ] ): Specifies that query optimizer hints can be used.
- OUTPUT <column_list> INTO <table_variable_or_temp_table>: Returns information about which rows were affected by the DELETE statement. This clause is available starting with SQL Server 2008.
Description
The DELETE statement is used to remove records from a database table. You can delete single rows, multiple rows, or all rows from a table.
When deleting from a table that has foreign key constraints referencing it, you must ensure that no rows in the referencing tables point to the rows being deleted. Otherwise, the deletion will fail unless the foreign key constraint is set to ON DELETE CASCADE or ON DELETE SET NULL.
Example 1: Deleting a specific row
This example deletes the row from the Products table where the ProductID is 7.
DELETE FROM Production.Product
WHERE ProductID = 7;Example 2: Deleting multiple rows based on a condition
This example deletes all products from the Products table that have a ListPrice less than $10.
DELETE FROM Production.Product
WHERE ListPrice < 10.00;Example 3: Deleting rows using a JOIN
This example deletes orders from the SalesOrderHeader table that were placed by customers in the 'Europe' territory.
DELETE soh
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.Customer AS c
ON soh.CustomerID = c.CustomerID
INNER JOIN Person.CountryRegion AS cr
ON c.CountryRegionCode = cr.CountryRegionCode
WHERE cr.Name = 'Europe';Example 4: Deleting all rows from a table
This statement deletes all rows from the SalesOrderDetail table. This is equivalent to using the TRUNCATE TABLE statement, but DELETE logs each row deletion.
DELETE FROM Sales.SalesOrderDetail;Note
Using DELETE without a WHERE clause will remove all rows from the table. This operation can be very time-consuming and resource-intensive for large tables. Consider using TRUNCATE TABLE for a faster way to remove all rows, as it is minimally logged.
Tip
When deleting a large number of rows, consider performing the delete operation in batches to reduce the transaction log size and minimize the impact on system performance.
Permissions
Requires DELETE permission on the table.