Microsoft SQL Server 2012 Bible

Updating Data

SQL's UPDATE command is an incredibly powerful tool. What used to take dozens of lines of code with multiple nested loops now takes a single statement. Even better, SQL is not a true language — it's a declarative language. The SQL code is describing to the Query Optimizer only what you want to do. The Query Optimizer then develops a cost-based, optimized query execution plan to accomplish the task. It determines which tables to fetch and in which order, how to merge the joins, and which indexes to use. It does this based on several factors, including the current data-population statistics, the indexes available, how they relate to the data population within the table, and table sizes. The Query Optimizer 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 ...