Microsoft SQL Server 2012 Bible

Deleting Data

The DELETE command is dangerously simple. In its basic form, it deletes all the rows from a table. Because the DELETE command is a row-based operation, it doesn't require specifying any column names. The first FROM is optional, as are the second FROM and the WHERE conditions. However, although the WHERE clause is optional, it is the primary subject of concern when you use the DELETE command. Following is an abbreviated syntax for the DELETE command:

DELETE [FROM] schema.Table
 [FROM data sources]
 [WHERE condition(s)];

Everything is optional except the actual DELETE command and the table name. The following command would delete all data from the Address table — no questions asked and no second chances:

DELETE
 FROM AdventureWorks.dbo.Address1;

SQL Server has no inherent UNDO command. When a transaction is committed, that's it. That's why the WHERE clause is so important when you're deleting.

By far, the most common use of the DELETE command is to delete a single row. The primary key is usually the means to select the row:

 USE AdventureWorks;
DELETE FROM dbo.Address
 WHERE AddressID = 1;

If you wanted to completely remove data from a table, the TRUNCATE option is available:

TRUNCATE TABLE dbo.Address

Caution

Please be careful when executing a TRUNCATE statement. It removes all data from a table and there is not an UNDO in SQL Server.

Referencing Multiple Data Sources While Deleting

You can use two techniques for referencing multiple data sources while deleting ...