Deleting Rows with DELETE

The DELETE statement removes one or more rows in a table or view. A simplified form of the DELETE syntax is:

DELETE table_or_view FROM table_sources WHERE search_condition

table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in 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 table_or_view. Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.

Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement.

To delete rows using DELETE

Transact-SQL

This script shows the three DELETE statements needed to delete the rows associated with products supplied by the company named Lyngbysild in the Northwind database. This would not be a typical business operation because it involves deleting lines from existing orders, but it does show a series of deletes of differing complexity.

USE Northwind
GO
DELETE [Order Details]
FROM Suppliers, Products
WHERE Products.SupplierID = Suppliers.SupplierID
  AND Suppliers.CompanyName = 'Lyngbysild'
  AND [Order Details].ProductID = Products.ProductID
GO
DELETE Products
FROM Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID
  AND Suppliers.CompanyName = 'Lyngbysild'
GO
DELETE Suppliers
WHERE CompanyName = 'Lyngbysild'
GO

See Also

DROP TABLE