Microsoft® SQL Server® 2012 Bible

309

Chapter 12: Modifying Data In SQL Server

12

AS DECIMAL(7, 2)) / 365.25 AS INT) * .1)

-- months in position

 + (CAST(CAST(DATEDIFF(d, DatePosition, '20120625')

AS DECIMAL(7, 2)) / 365.25 * 12 AS INT) * .02)

-- Performance Rating minimum

 + (CASE WHEN Employee.PerformanceRating >= 2

THEN Employee.PerformanceRating

ELSE 0

END * .5))

-- Raise Factor

* RaiseFactor) / 100 ))

FROM dbo.Employee

JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

A quick check of the data confi rms that the update was successful:

SELECT FirstName, LastName, Salary

FROM dbo.Employee

Result:

FirstName LastName Salary

------------ ------------- -----------

Sam Smith 59097.60

Slim Nelson 84427.20

Sally Ball 48150.00

Jeff Kelly 89828.00

Jo Guelzow 141000.00

Missy Anderson 108395.00

Sam Reagan 79207.50

Hank Adams 35603.20

The fi nal step of the exercise is to clean up the sample tables:

DROP TABLE dbo.Employee, dbo.Dept;

This sample code pulls together techniques from many of the previous chapters: creating

and dropping tables, CASE expressions, joins, and date scalar functions, not to mention the

inserts and updates from this chapter. The example is long because it demonstrates more

than just the UPDATE statement. It also shows the typical process of developing a complex

UPDATE, which includes the following:

1. Check the available data: The fi rst SELECT joins employee and dept and lists all
   the columns required for the formula.


2. Test the formula: The second SELECT is based on the initial SELECT and assem-
   bles the formula from the required rows. From this data, a couple of rows can be
   hand-tested against the specs, and the formula verifi ed.


3. Perform the update: After the formula is constructed and verifi ed, the formula is
   edited into an UPDATE statement and executed.

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

http://www.it-ebooks.info