UPDATE

The UPDATE statement can be a little more complex than INSERT or DELETE because you need to find the rows that will become the target of the updates, and then specify the columns to be updated. There will also be times when you specify a FROM clause, updating data residing in more than one table. Another key clause of the UPDATE statement is the SET clause, which specifies the columns that are the target for the updates and the values to be used. A simple UPDATE statement might look like this:

 UPDATE Orders SET ShipPostalCode = '33428' WHERE CustID = 'VINET' 

The entire syntax for the UPDATE, however, is as follows:

 [ WITH <common_table_expression> [...n ] ] UPDATE     [ TOP ( expression ) [ PERCENT ] ]     { <object> | rowset_function_limited      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]     }     SET        { column_name = { expression | DEFAULT | NULL }          | { udt_column_name.{ { property_name = expression                                | field_name = expression }                               | method_name ( argument [ ,...n ] )                              }            }          | column_name { .WRITE ( expression , @Offset , @Length ) }          | @variable = expression          | @variable = column = expression [ ,...n ]        } [ ,...n ]      [ <OUTPUT Clause> ]      [ FROM{ <table_source> } [ ,...n ] ]     [ WHERE { <search_condition>             | { [ CURRENT OF                   { { [ GLOBAL ] cursor_name }                       | cursor_variable_name                   }                  ]                          }             }     ]     [ OPTION ( <query_hint> [ ,...n ] ) ] [ ; ] <object> ::= {     [ server_name.database_name.schema_name.     | database_name.[ schema_name ].     | schema_name.     ]          table_or_view_name}

The UPDATE arguments are explained in BOL in the same fashion as INSERT. But some further investigation on several clauses and arguments is warranted here. The bullet list that follows here lists the various forms in which UPDATE can be used.

• UPDATE with information from another table

• UPDATE with the TOP clause

• UPDATE with the OUTPUT clause

• UPDATE with the WITH common_table_expression clause

• UPDATE with the .WRITE clause to modify data in an nvarchar(max) column

• UPDATE with .WRITE to add and remove data in an nvarchar(max) column

• UPDATE with OPENROWSET to modify a varchar(max) or varbinary(max) column

Targets of the UPDATE

You can update values in all base tables, derivatives, cursor result sets, and views, as well as in the table variable and the rowset functions, as long as only one target is specified in the UPDATE. The rowset functions can be either the OPENQUERY or OPENROWSET functions. You can also use the UPDATE in user-defined functions (UDFs) only if the set being updated is an instance of the table variable. And the various shades of target table types are not treated any differently by the UPDATE. Reference them as if the variable or function was nothing more than an actual base table.

Column Names

The column_name argument specifies the value at the column field to be updated. The column name must be in the target table specified in the UPDATE. You can qualify each column with the table name in the standard qualifying dot notation. For example, the code

 UPDATE [Orders] SET [Orders].CustomerID = 'VITEN' WHERE [Orders]. CustomerID = 'VINET'

changes five rows in the northwind.orders table. The where clause specifies which rows meet the update criterion. In this case, all rows that have "VITEN" as the CustomerID qualify for the update, and VITEN is changed to VINET.

The SET Value

In the preceding example, the SET value is 'VITEN' (after the =), which can be a string, or a number, or some scalar value. However, the value to be updated by SET can also be derived from an expression that can be calculated or a value returned from a SELECT statement, or the return of a function. Consider the following code, which is valid:

 UPDATE Orders SET ShippedDate = GetDate()   WHERE CustomerID = 'VINET'

All records meeting the search criterion of CustomerID='VINET' are updated to reflect the ship date of the date and time the statement is executed. In this example, I have simply used a standard T-SQL function to return a value for the SET clause. The next example provides the result of a calculation as the new SET value:

 UPDATE Products SET UnitPrice = UnitPrice * 10/100 + UnitPrice   WHERE ProductName = 'Chai'

In this code, I raised the price of Chai by 10 percent. The functions or expression you use in the SET clause can thus be as complex as you need them to be; the ultimate objective is that the expression boils down to a single value. Of course the value should be of the same type required by the column, or the value should be converted automatically or explicitly, unless the column type is of sql_variant and your value is not an image or text.

The following SET expression is a little more complex, but it illustrates what you can do to arrive at the value you need for the column data. Let’s imagine that a Northwind customer with two branches needed to be recorded in the Orders table under one CustomerID assigned to the phone number (503) 555–7555. We can change the CustomerID with the following code:

 UPDATE Orders SET  CustomerID = (SELECT TOP 1 CustomerID  FROM Customers WHERE Phone = '[phone]')  WHERE CustomerID = 'VINET'

Notice that the SELECT clause is between parentheses-the statement fails without the brackets. Also notice how we do not need to qualify the column names in the entire statement, because the SELECT clause is unambiguous by virtue of the FROM line.

We can also provide the result of a calculation or complex search expression in the final WHERE clause of the UPDATE statement.

Conditional Updates

Using CASE expressions in UPDATE statements can result in some fancy conditional code, especially if used in stored procedures. The following code updates several prices in Northwind’s product table:

 UPDATE Products SET UnitPrice =   (CASE SupplierID     WHEN '4' THEN UnitPrice * 5/100 + UnitPrice     WHEN '7' THEN UnitPrice * 30/100 + UnitPrice     WHEN '23' THEN UnitPrice − UnitPrice * 10/100    END)