|
Use this statement to modify existing rows in database tables.
UPDATE [ FIRST | TOP n ] table-list
SET set-item, ...
[ FROM table-list ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ], ... ]
[ OPTION( query-hint, ... ) ]
UPDATE table-name
SET set-item, ...
VERIFY ( column-name, ... ) VALUES ( expression, ... )
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ], ... ]
[ OPTION( query-hint, ... ) ]
UPDATE table
PUBLICATION publication
{ SUBSCRIBE BY expression
| OLD SUBSCRIBE BY expression NEW SUBSCRIBE BY expression
}
WHERE search-condition
set-item :
column-name [.field-name...] = expression
| @variable-name = expression
query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value
option-name : identifier
option-value : hostvar (indicator allowed), string, identifier, or number
UPDATE clause The table is either a base table, a temporary table, or a view. Views can be updated unless the SELECT statement defining the view contains a GROUP BY clause or aggregate function, or involves a UNION statement.
FIRST or TOP clause Primarily for use with the ORDER BY clause, this clause allows you to update only a certain subset of the rows that satisfy the WHERE clause. You cannot use a variable as input with FIRST or TOP.
SET clause The set clause specifies the columns and how the values are changed.
You can use the SET clause to set the column to a computed column value using this format:
SET column-name = expression, ...
Each named column is set to the value of the expression on the right hand side of the equal sign. There are no restrictions on the expression. If the expression is a column-name, the old value is used.
You can also use the SET clause to assign a variable using this format:
SET @variable-name = expression, ...
When assigning a variable, the variable must already be declared, and its name must begin with the "at" sign (@). Variable and column assignments can be mixed together, and any number can be used. If a name on the left side of an assignment in the SET list matches a column in the updated table as well as the variable name, the statement will update the column.
Following is an example of part of an UPDATE statement. It assigns a variable in addition to updating the table:
UPDATE T SET @var = expression1, col1 = expression2 WHERE...
This is equivalent to:
SELECT @var = expression1 FROM T WHERE... ; UPDATE T SET col1 = expression2 WHERE...
FROM clause The optional FROM clause allows tables to be updated based on joins. If the FROM clause is present, the WHERE clause qualifies the rows of the FROM clause. Data is updated only in the table list of the UPDATE clause.
If a FROM clause is used, it is important to qualify the table name the same way in both parts of the statement. If a correlation name is used in one place, the same correlation name must be used elsewhere. Otherwise, an error is generated.
This clause is allowed only if ansi_update_constraints is set to Off. See ansi_update_constraints option [compatibility].
For a full description of joins, see Joins: Retrieving Data from Several Tables.
For more information, see FROM clause.
WHERE clause If a WHERE clause is specified, only rows satisfying the search condition are updated. If no WHERE clause is specified, every row is updated.
ORDER BY clause Normally, the order in which rows are updated does not matter. However, in conjunction with the FIRST or TOP clause the order can be significant.
You cannot use ordinal column numbers in the ORDER BY clause.
You must not update columns that appear in the ORDER BY clause unless you set the ansi_update_constraints option to Off. See ansi_update_constraints option [compatibility].
OPTION clause This clause provides hints as to how to process the query. The following query hints are supported:
MATERIALIZED VIEW OPTIMIZATION 'option-value' Use the MATERIALIZED VIEW OPTIMIZATION clause to specify how the optimizer should make use of materialized views when processing the query. The specified option-value overrides the materialized_view_optimization database option for this query only. Possible values for option-value are the same values available for the materialized_view_optimization database option. See materialized_view_optimization option [database].
FORCE OPTIMIZATION When a query specification contains only simple queries (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row), it typically bypasses cost-based optimization during processing. In some cases you may want cost-based optimization to occur. For example, if you want materialized views to be considered during query processing, view matching must occur. However, view matching only occurs during cost-base optimization. If you want cost-based optimization to occur for a query, but your query specification contains only simple queries, specify the FORCE OPTIMIZATION option to ensure that the optimizer performs cost-based optimization on the query.
Similarly, specifying the FORCE OPTIMIZATION option in a SELECT statement inside of a procedure forces the use of the optimizer for any call to the procedure. In this case, plans for the statement are not cached. For more information on simple queries and view matching, see Phases of query processing, and Improving performance with materialized views.option-name = option-value Specify an option setting that takes precedence over any public or temporary option settings that are in effect, for this statement only. The supported options are:
Case sensitivity Character strings inserted into tables are always stored in the same case as they are entered, regardless of whether the database is case sensitive or not. A CHAR data type column updated with a string Value is always held in the database with an uppercase V and the remainder of the letters lowercase. SELECT statements return the string as Value. If the database is not case sensitive, however, all comparisons make Value the same as value, VALUE, and so on. Further, if a single-column primary key already contains an entry Value, an INSERT of value is rejected, as it would make the primary key not unique.
Updates that leave a row unchanged If the new value does not differ from the old value, no change is made to the data. However, BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether or not the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value.
Syntax 1 of the UPDATE statement modifies values in rows of one or more tables. Syntax 2 and 3 are applicable only to SQL Remote.
Syntax 2 is intended for use with SQL Remote only, in single-row updates of a single table executed by the Message Agent. The VERIFY clause contains a set of values that are expected to be present in the row being updated. If the values do not match, any RESOLVE UPDATE triggers are fired before the UPDATE proceeds. The UPDATE does not fail simply because the VERIFY clause fails to match.
Syntax 3 of the UPDATE statement is used to implement a specific SQL Remote feature, and is to be used inside a BEFORE trigger. It provides a full list of SUBSCRIBE BY values any time the list changes. It is placed in SQL Remote triggers so that the database server can compute the current list of SUBSCRIBE BY values. Both lists are placed in the transaction log.
The Message Agent uses the two lists to make sure that the row moves to any remote database that did not have the row and now needs it. The Message Agent also removes the row from any remote database that has the row and no longer needs it. A remote database that has the row and still needs it is not be affected by the UPDATE statement.
For publications created using a subquery in a SUBSCRIBE BY clause, you must write a trigger containing syntax 3 of the UPDATE statement to ensure that the rows are kept in their proper subscriptions.
Syntax 3 of the UPDATE statement allows the old SUBSCRIBE BY list and the new SUBSCRIBE BY list to be explicitly specified, which can make SQL Remote triggers more efficient. In the absence of these lists, the database server computes the old SUBSCRIBE BY list from the publication definition. Since the new SUBSCRIBE BY list is commonly only slightly different from the old SUBSCRIBE BY list, the work to compute the old list may be done twice. By specifying both the old and new lists, you can avoid this extra work.
The SUBSCRIBE BY expression is either a value or a subquery.
Syntax 3 of the UPDATE statement makes an entry in the transaction log, but does not change the database table.
Updating a significant amount of data using the UPDATE statement also updates column statistics.
Must have UPDATE permission for the columns being modified.
Column statistics are updated.
SQL/2003 Syntax 1 is a core feature, except for the FROM and ORDER BY clauses, which are vendor extensions. Syntax 2 and 3 are vendor extensions for use only with SQL Remote.
To enforce SQL/2003 compatibility, ensure that the ansi_update_constraints option is set to Strict. See ansi_update_constraints option [compatibility].
Using the sample database, this example transfers employee Philip Chin (employee 129) from the sales department to the marketing department.
UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 129;
Using the sample database, this example renumbers all existing sales orders by subtracting 2000 from the ID.
UPDATE SalesOrders AS orders SET orders.ID = orders.ID - 2000 ORDER BY orders.ID ASC;
This update is possible only if the foreign key of the SalesOrderItems table (referencing the primary key SalesOrders.ID) is defined with the action ON UPDATE CASCADE. The SalesOrderItems table is then updated as well.
For more information on foreign key properties, see ALTER TABLE statement and CREATE TABLE statement.
Using the sample database, this example changes the price of a product at isolation level 2, rather than using the current isolation level setting of the database.
UPDATE Products SET UnitPrice = 7.00 WHERE ID = 501 OPTION( isolation_level = 2 );