O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required