Microsoft® SQL Server® 2012 Bible

305

Chapter 12: Modifying Data In SQL Server

12

One way to envision the FROM clause is to picture the joins merging all the tables into a

new super-wide result set. Then the rest of the SQL statement sees only that new result

set. Although that is what’s happening in the FROM clause, the actual UPDATE operation is

functioning not on the new result set, but only on the declared UPDATE table.

The following queries fi rst adds the HasPurchased column to the Customer table in the

AdventureWork table; then the next query uses the FROM clause to access the Customer

and SalesOrderHeader tables. The JOIN limits the query to only those customer rows

that have placed orders. The UPDATE command updates only the Customer table:

use AdventureWorks

go

ALTER TABLE Sales.Customer

ADD HasPurchased bit;

UPDATE Sales.Customer

SET HasPurchased = 1

FROM Sales.Customer c

INNER JOIN Sales.SalesOrderHeader soh

ON c.CustomerID = soh.CustomerID

The UPDATE FROM syntax is a T-SQL extension and not standard ANSI SQL 92. If the database will possibly be

ported to another database platform in the future, then use a subquery to select the correct rows:

USE AdventureWorks

go

UPDATE Sales.Customer

SET HasPurchased = 1

FROM Sales.Customer c

WHERE CustomerID IN (SELECT CustomerID FROM Sales.SalesOrderHeader)

For a real-life example, suppose all employees will soon be granted a generous across-the-

board raise (OK, so it’s not a real-life example) based on department, length of service in

the position, performance rating, and length of time with the company. If the percent-

age for each department is stored in the Department table, SQL can adjust the salary for

every employee with a single UPDATE statement by joining the Employee table with the

Department table and pulling the Department raise factor from the joined table. Assume

the formula is as follows:

2 + (((Years in Company * .1) + (Months in Position * .02)

 + ((PerformanceFactor * .5 ) if over 2))

* Department RaiseFactor)

c12.indd 305c12.indd 305 7/30/2012 4:42:40 PM7/30/2012 4:42:40 PM

http://www.it-ebooks.info