T-SQL » Statements » DELETE

Syntax:
DELETE [ FROM ] { table_name | view_name }
    [ FROM { < table_source > } [ ,...n ] ]
    [ WHERE { < criteria > } ]
table_name
Specifies the name of the table that the delete operation is to be performed on.
view_name
Specifies the name of the view that the delete operation is to be performed on.
table_source
A parameter that allows the specification of data from table_source and deletes corresponding rows from the table in the first FROM clause.
criteria
Specifies the restricting condition(s) for the row(s) to be deleted.

The DELETE statement creates a query that removes records from one or more tables.

A DELETE is permanent!

After you remove records using a DELETE statement, you cannot undo the operation. To check which records will be deleted, examine the results of a SELECT query that uses the same criteria.

It is also important to understand, that a DELETE statement deletes entire records, not just data in specified fields. If you just want to delete certain fields, use an UPDATE query that changes the value to NULL.

Examples

Code:
DELETE FROM Residents;
Output:
(10 row(s) affected)
Explanation:

This example demonstrates how to totally empty a table of all records while leaving the table structure and properties, such as attributes and indexes, intact.

Language(s): MS SQL Server
Code:
DELETE FROM Residents
WHERE Occupation = 'Teacher';
Output:
(3 row(s) affected)
Explanation:

This example is more specific and only deletes those records that meet certain criteria.

Language(s): MS SQL Server

See Also: