UPDATE Statement

UPDATE Statement (version 6.5)

Changes data in existing rows, either by adding new data or by modifying existing data. You must use aliases to distinguish between updates of tables and columns with the same names.

For additional syntax information for the UPDATE statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

UPDATE {table_name | view_name}
SET
    [column_name = {column_list | variable_list | variable_and_column_list}
        [, column_name2 = {column_list2 | variable_list2 |
variable_and_column_list2}
    ...[, column_nameN = {column_listN | variable_listN
                | variable_and_column_listN}]]
                | expression]
[FROM {table_name1 | view_name1}[(optimizer_hints)]
    [, {table_name2 | view_name2}[(optimizer_hints)]
    [..., {table_name16 | view_name16}[(optimizer_hints)]]]]

where

table_name | view_name = [[database_name.]owner.]{table_name | view_name}

Specifies the name of the table or view used in the UPDATE statement.

SET

Is a required keyword used to introduce the list of column or variable clauses to be updated.

column_name =

Specifies a column from the table (table_name) or view (view_name).

column_list =

column_name = {expression | DEFAULT | NULL}

variable_list =

variable_name = {expression | NULL}

expression

Is a column_name, constant, function (aggregate functions are not allowed), or any combination of column_names, constants, and functions connected by an operator(s) or a subquery.

DEFAULT

Specifies the default value for that column.

variable_and_column_list = variable_name = column_name = {expression | NULL | DEFAULT}

Specifies local variables as well as columns.

WHERE clause = WHERE {search_conditions | CURRENT OF cursor_name}

Is used to do a searched update (using search_conditions) or a positioned update (using CURRENT OF cursor_name).

search_conditions

Specifies the criteria for a searched update.

CURRENT OF cursor_name

Specifies the criteria for a positioned update against the current row within the specified cursor_name.

FROM

Specifies the table(s) and view(s) that are used in the SELECT statement. FROM is required except when the table_name or view_name contains only constants, variables, and arithmetic expressions (no column names). The FROM clause supports a maximum of 16 tables and views. Tables in subqueries are included in this total.

table_name1 | view_name1 = [[database.]owner.]{table_name. | view_name.}

Specifies the name(s) of the table(s) and view(s) that are used in the UPDATE statement. If the list contains more than one table or view, delimit the names with commas. If the table(s) or view(s) exist in another database(s), use a fully qualified table or view name (database_name.owner.object_name).

Each table_name or view_name can be given an alias, either for convenience or to distinguish a table or view in a self-join or subquery. Aliases (when defined) must be used for ambiguous column references and must match the alias reference (the full table name cannot be used if an alias has been defined). To use an alias, specify the object name, a space, and then the alias name as follows:

UPDATE titles
SET t.ytd_sales = t.ytd_sales + qty
FROM titles t, sales s
WHERE t.title_id = s.title_id

The order of the tables and views after the FROM keyword does not affect the results set returned.

optimizer_hints

Specify that a locking method, an index, or no index (table scan) be used by the optimizer with this table and for this UPDATE. Although this is an allowable option, the optimizer will usually pick the best optimization method.

An optimizer_hint can be one or more of the following.

Option Description

TABLOCK Performs an exclusive lock on the table that is held until the end of command. TABLOCKX can be substituted for TABLOCK.

PAGLOCK Performs exclusive page locks where a single exclusive table lock would normally be taken.

TABLOCKX Performs an exclusive lock on the table that is held until the end of command. TABLOCK may be substituted for TABLOCKX.



Option	Description
TABLOCK	Performs an exclusive lock on the table that is held until the end of command. TABLOCKX can be substituted for TABLOCK.
PAGLOCK	Performs exclusive page locks where a single exclusive table lock would normally be taken.
TABLOCKX	Performs an exclusive lock on the table that is held until the end of command. TABLOCK may be substituted for TABLOCKX.

Important Some of the optimizer hints can be used in combination. (For example, TABLOCK and PAGLOCK). When multiple options are specified, separate them with a space; the more restrictive option will take precedence.

Remarks

The update table must be unambiguous for updates to occur. If two tables in a query have the same column name, use aliases to distinguish them.

If aliases are used, the UPDATE statement will cause an error when the table that should be updated is ambiguous. Trace flag 110 will disable these changes.

For more information about trace flag 110, see Trace Flags.

Examples

A. Reference the Same Table Twice in the UPDATE Statement

This example references the same table twice in the WHERE clause or different tables with the same name. In earlier versions, changes were made to the first occurrence of the table. In version 6.5 an error message is returned.

Table stockref

id	qty	type
1	10	'a'
2	10	'a'
3	10	'a'
4	10	'a'
1	30	'b'
2	40	'b'
3	50	'b'
4	60	'b'

update stockref 
set s2.qty = s1.qty
from stockref s1, stockref s2
where s1.id = s2.id and 
      s1.type = 'a' and 
      s2.type = 'b'

This SELECT statement divides the stockref table into two parts, the 'a' range and the 'b' range, matches them on the id field and then copies the qty value from the 'b' range and replaces the qty values in the 'a' range. The UPDATE stockref section of the syntax is ambiguous because it does not specify whether to update the 'a' range or the 'b' range.

The SET s2.qty = s1.qty section of syntax specifies which range to update. In order to avoid an error, use an alias on the ambiguous table reference to indicate which range of the stockref table to update.

UPDATE s2
SET s2.qty = s1.qty
FROM stockref s1, stockref s2
WHERE s1.id = s2.id AND
s1.type = 'a' AND
s2.type = 'b

B. Use Optimizer Hints

This example uses the PAGLOCK optimizer_hint to use a shared page lock when updating the author's last name from Yokohama to Yokomoto in the authors table.

UPDATE authors
SET au_lname = 'Yokohama'
FROM authors(PAGLOCK)
WHERE au_lame = 'Yokomoto'