Microsoft® SQL Server® 2012 Bible

311

Chapter 12: Modifying Data In SQL Server

12

Referencing Multiple Data Sources While Deleting

You can use two techniques for referencing multiple data sources while deleting rows: the

double FROM clause and subqueries.

The UPDATE command uses the FROM clause to join the updated table with other tables for

more fl exible row selection. The DELETE command can use the exact same technique. When

using this method, the fi rst optional FROM can make it look confusing. To improve readabil-

ity and consistency, you can omit the fi rst FROM in your code.

For example, the following DELETE statement ignores the fi rst FROM clause and uses the

second FROM clause to join Product with ProductCategory so that the WHERE clause

can fi lter the DELETE based on the ProductCategoryName. This query creates the dbo.

Product table then removes all jerseys from the Product table:

USE AdventureWorks;

SELECT *

INTO dbo.Product

FROM Production.Product

DELETE dbo.Product

FROM dbo.Product p

INNER JOIN Production.ProductSubcategory s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.Name = 'Jerseys'

The second method looks more complicated at fi rst glance, but it’s ANSI standard and the

preferred method. A correlated subquery actually selects the rows to be deleted, and the

DELETE command just picks up those rows for the DELETE operation. It’s a clean query:

DELETE FROM dbo.Product

WHERE EXISTS

(SELECT *

FROM Production.ProductSubcategory AS ps

WHERE ps.ProductSubcategoryID = Product.ProductSubcategoryID

AND ps.Name = 'Jerseys');

In terms of performance, both methods generate the exact same query execution plan.

As with the UPDATE command’s FROM clause, the DELETE command’s second FROM clause is not an ANSI SQL

standard. If portability is important to your project, then use a subquery to reference additional tables.

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

http://www.it-ebooks.info