Microsoft® SQL Server® 2012 Bible

310

Part II: Building Databases and Working with Data

The SQL UPDATE command is powerful. The terribly complex record sets and nested loops

that were painfully slow and error-prone have been replaced with UPDATE statements and

creative joins that worked well. As a result, execution times have been reduced from hours

to a few seconds.

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

ing any column names. The fi rst 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 follow-

ing 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

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

UNDO in SQL Server.

c12.indd 310c12.indd 310 7/30/2012 4:42:41 PM7/30/2012 4:42:41 PM

http://www.it-ebooks.info