UPDATE
Changes existing data in a table.
Syntax
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]
{ { [ FROM { < table_source > } [ ,...n ] ]
[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| < table_source > CROSS JOIN < table_source >
| < joined_table >
< join_type > ::=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]
[ < join_hint > ]
JOIN
< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
< query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| {LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
Arguments
table_name
Is the name of the table to update. The name can be qualified with the linked server, database, and owner name if the table is not in the current server or database, or is not owned by the current user.
WITH ( < table_hint_limited > [ ...n ] )
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. READPAST, NOLOCK, and READUNCOMMITTED are not allowed. For information about table hints, see FROM.
view_name
Is the name of the view to update. The view referenced by view_name must be updatable. The modifications made by the UPDATE statement cannot affect more than one of the base tables referenced in the FROM clause of the view. For more information on updatable views, see CREATE VIEW.
rowset_function_limited
Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about capabilities needed by the provider, see UPDATE and DELETE Requirements for OLE DB Providers. For more information about the rowset functions, see OPENQUERY and OPENROWSET.
SET
Specifies the list of column or variable names to be updated.
column_name
Is a column that contains the data to be changed. column_name must reside in the table or view specified in the UPDATE clause. Identity columns cannot be updated.
If a qualified column name is specified, the qualifier must match the table or view name in the UPDATE clause. For example, this is valid:
UPDATE authors
SET authors.au_fname = 'Annie'
WHERE au_fname = 'Anne'
A table alias specified in a FROM clause cannot be used as a qualifier in SET column_name. For example, this is not valid:
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
To make the example work, remove the t. alias from the column name.
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
expression
Is a variable, literal value, expression, or a parenthesized subSELECT statement that returns a single value. The value returned by expression replaces the existing value in column_name or @variable.
DEFAULT
Specifies that the default value defined for the column is to replace the existing value in the column. This can also be used to change the column to NULL if the column has no default and is defined to allow null values.
@variable
Is a declared variable that is set to the value returned by expression.
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
FROM < table_source >
Specifies that a table is used to provide the criteria for the update operation. For more information, see FROM.
table_name [ [ AS ] table_alias ]
Is the name of a table to provide criteria for the update operation.
If the table being updated is the same as the table in the FROM clause, and there is only one reference to the table in the FROM clause, table_alias may or may not be specified. If the table being updated appears more than one time in the FROM clause, one (and only one) reference to the table must not specify a table alias. All other references to the table in the FROM clause must include a table alias.
view_name [ [ AS ] table_alias ]
Is the name of a view to provide criteria for the update operation. A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause.
WITH ( < table_hint > [ ...n ] )
Specifies one or more table hints for a source table. For information about table hints, see "FROM" in this volume.
rowset_function [ [ AS ] table_alias ]
Is the name of any rowset function and an optional alias. For information about a list of rowset functions, see Rowset Functions.
derived_table
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
column_alias
Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.
< joined_table >
Is a result set that is the product of two or more tables, for example:
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
< join_type >
Specifies the type of join operation.
INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [ OUTER ]
Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.
RIGHT [ OUTER ]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.
FULL [ OUTER ]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
< join_hint >
Specifies a join hint or execution algorithm. If <join_hint> is specified, INNER, LEFT, RIGHT, or FULL must also be explicitly specified. For more information about joint hints, see FROM.
JOIN
Indicates that the specified tables or views should be joined.
ON < search_condition >
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
For more information about search conditions and predicates, see Search Condition.
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if the tables to be joined were simply listed in the FROM clause and no WHERE clause was specified.
WHERE
Specifies the conditions that limit the rows that are updated. There are two forms of update based on which form of the WHERE clause is used:
Searched updates specify a search condition to qualify the rows to delete.
Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor.
< search_condition >
Specifies the condition to be met for the rows to be updated. The search condition can also be the condition upon which a join is based. There is no limit to the number of predicates that can be included in a search condition. For more information about predicates and search conditions, see Search Condition.
CURRENT OF
Specifies that the update is performed at the current position of the specified cursor.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name refers to the global cursor if GLOBAL is specified. If GLOBAL is not specified, cursor_name refers to the local cursor. The cursor must allow updates.
cursor_variable_name
Is the name of a cursor variable. cursor_variable_name must reference a cursor that allows updates.
OPTION ( < query_hint > [ ,...n ] )
Specifies that optimizer hints are used to customize SQL Server's processing of the statement.
{ HASH | ORDER } GROUP
Specifies that the aggregations specified in the GROUP BY or COMPUTE clause of the query should use hashing or ordering.
{ LOOP | MERGE | HASH |} JOIN
Specifies that all join operations are performed by loop join, merge join, or hash join in the whole query. If more than one join hint is specified, the query optimizer selects the least expensive join strategy for the allowed ones. If, in the same query, a join hint is also specified for a specific pair of tables, it takes precedence in the joining of the two tables.
{ MERGE | HASH | CONCAT } UNION
Specifies that all UNION operations should be performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.
Note If a join hint is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any join hint specified in the OPTION clause.
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax should be preserved during query optimization.
MAXDOP number
Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size at the expense of performance. If no such plan is possible, the query optimizer returns an error rather than deferring error detection to query execution. Rows may contain variable-length columns; SQL Server allows rows to be defined whose maximum potential size is beyond the ability of SQL Server to process. Usually, despite the maximum potential size, an application stores rows that have actual sizes within the limits that SQL Server can process. If SQL Server encounters a row that is too long, an execution error is returned.
KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will be recompiled less frequently when there are multiple updates to a table.
Remarks
UPDATE statements are allowed in the body of user-defined functions only if the table being modified is a table variable.
A table variable, in its scope, may be accessed like a regular table. Thus, a table variable may be used as the table in which data is updated in an UPDATE statement.
A four-part name constructed with the OPENDATASOURCE function as the server-name part may be used as a table source in all places a table name can appear in UPDATE statements.
If an update to a row violates a constraint or rule, if it violates the NULL setting for the column, or if the new value is an incompatible data type, the statement is canceled, an error is returned, and no records are updated.
When an UPDATE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) during expression evaluation, the update is not performed. The remainder of the batch is not executed, and an error message is returned.
If an update to a column or columns participating in a clustered index causes the size of the clustered index and the row to exceed 8,060 bytes, the update fails and an error message is returned.
When an INSTEAD-OF trigger is defined on UPDATE actions against a table, the trigger executes instead of the UPDATE statement. Previous versions of SQL Server only support AFTER triggers defined on UPDATE and other data modification statements.
If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.
Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page unless updating the column with NULL.
Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.
All char and nchar columns are right-padded to the defined length.
The setting of the SET ROWCOUNT option is ignored for UPDATE statements against remote tables and local and remote partitioned views.
If ANSI_PADDING is set OFF, all trailing spaces are removed from data inserted into varchar and nvarchar columns, except in strings containing only spaces. These strings are truncated to an empty string. If ANSI_PADDING is set ON, trailing spaces are inserted. The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically set ANSI_PADDING ON for each connection. This can be configured in ODBC data sources or by setting connection attributes or properties.
A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can be more accurate than a searched update that uses a WHERE <search_condition> clause to qualify the rows to be updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated (in other words, if the UPDATE statement is not deterministic). For example, given the UPDATE statement in the following script, both rows in table s meet the qualifications of the FROM clause in the UPDATE statement, but it is undefined which row from s is used to update the row in table t.
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
The same problem can occur when combining the FROM and WHERE CURRENT OF clauses. In this example, both rows in table t2 meet the qualifications of the FROM clause in the UPDATE statement. It is undefined which row from t2 is to be used to update the row in table t1.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go
DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1
OPEN abc
FETCH abc
UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
GO
Setting Variables and Columns
Variable names can be used in UPDATE statements to show the old and new values affected. This should only be used when the UPDATE statement affects a single record; if the UPDATE statement affects multiple records, the variables only contain the values for one of the updated rows.
Permissions
UPDATE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.
SELECT permissions are also required for the table being updated if the UPDATE statement contains a WHERE clause, or if expression in the SET clause uses a column in the table.
Examples
A. Use a simple UPDATE
These examples show how all rows can be affected if a WHERE clause is eliminated from an UPDATE statement.
If all the publishing houses in the publishers table move their head offices to Atlanta, Georgia, this example shows how the publishers table can be updated.
UPDATE publishers
SET city = 'Atlanta', state = 'GA'
This example changes the names of all the publishers to NULL.
UPDATE publishers
SET pub_name = NULL
You can also use computed values in an update. This example doubles all prices in the titles table.
UPDATE titles
SET price = price * 2
B. Use the UPDATE statement with a WHERE clause
The WHERE clause specifies the rows to update. For example, consider the unlikely event that northern California is renamed Pacifica (abbreviated PC) and the people of Oakland vote to change the name of their city to Bay City. This example shows how to update the authors table for all former Oakland residents whose addresses are now out of date.
UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
You must write another statement to change the name of the state for residents of other northern California cities.
C. Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
This example assumes that only one set of sales is recorded for a given title on a given date and that updates are current. If this is not the case (if more than one sale for a given title can be recorded on the same day), the example shown here does not work correctly. It executes without error, but each title is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row twice.
In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown in this example:
UPDATE titles
SET ytd_sales =
(SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id
AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales
D. Use UPDATE with the TOP clause in a SELECT statement
This example updates the state column for the first 10 authors from the authors table.
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
|
