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
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