303
Chapter 12: Modifying Data In SQL Server
12
even considers current CPU performance, memory capacity, and hard-drive performance
when designing the plan. Writing code to perform the update row-by-row could never result
in that level of optimization.Updating a Single Table
The UPDATE command in SQL is straightforward and simple. It can update one column of
one row in a table, or every column in every row in the updated table, but the optional
FROM clause enables that table to be part of a complete complex data source with all the
power of the SQL SELECT.Following is how the UPDATE command works:UPDATE schema.Table
SET column = expression,
column = value...
[FROM data sources]
[WHERE conditions];The UPDATE command can update multiple rows but only one table. The SET keyword is
used to modify data in any column in the table to a new value. The new value can be a
hard-coded string literal, a variable, an expression, or even another column from the data
sources listed in the FROM portion of the SQL UPDATE statement.For a comprehensive list of expression possibilities, see Chapter 8, “Data Types, Expressions, and
Scalar Functions.”The WHERE clause is vital to any UPDATE statement. Without it, the entire table is updated.
If a WHERE clause is present, then only the rows not fi ltered out by the WHERE clause are
updated. Be sure to check and double-check the WHERE clause. Again, measure twice, cut
once. Remember, there is not an undo command.The following sample UPDATE resembles a typical real-life operation, altering the value of
one column for a single row. The best way to perform a single-row update is to fi lter the
UPDATE operation by referencing the primary key:USE AdventureWorks;
UPDATE dbo.Address
SET Address1 = '1970 Napa Court'
WHERE AddressID = 1The following SELECT statement confi rms the preceding UPDATE command:Use AdventureWorks
go
SELECT AddressID, Address1
FROM dbo.Address
WHERE AddressID = 1;c12.indd 303c12.indd 303 7/30/2012 4:42:40 PM7/30/2012 4:42:40 PM
http://www.it-ebooks.info