UPDATE statement

Modifies rows in database tables.

Syntax

Update rows using joins, views, and derived tables

UPDATE [ row-limitation ] table-expression [, ...] 
SET set-item[, ...]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ] , ...]
[ OPTION( query-hint, ... ) ]

row-limitation :
FIRST
| TOP { ALL | limit-expression } [ START AT startat-expression ]

limit-expression : simple-expression
startat-expression : simple-expression

simple-expression :
integer
| variable
| ( simple-expression )
| ( simple-expression { + | - | * } simple-expression )

table-expression : A table expression that can include joins, outer joins, views and derived tables. See the FROM clause topic.

set-item :
[ correlation-name.]column-name={ expression | DEFAULT }
| [ owner. ]table-name.column-name = { expression | DEFAULT }
| [ owner. ]@variable-name=expression

table-name : 
[ owner.]base-table-name
| temporary-table-name
| derived-table-name
| [ owner.]view-name

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| FORCE NO OPTIMIZATION
| option-name = option-value

option-name : identifier

option-value :
hostvar (indicator allowed)
| string
| identifier
| number

Update rows in a single table

UPDATE [ row-limitation ] table-name 
SET set-item[, ...]
FROM table-expression [, ...] ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ] , ...]
[ OPTION( query-hint, ... ) ]

table-name : 
[ owner.]table-name [ [ AS ] correlation-name ]
| [ owner.]view-name [ [ AS ] correlation-name ]
| derived-table

derived-table : 
( select-statement ) 
[ AS ] correlation-name [ ( column-name [, ...] ) ]

Parameters

UPDATE clause
When updating tables, table-expression can include temporary tables, global temporary tables, derived tables, or views. Views and derived tables can be updated unless they are non-updatable.

When updating using joins, views, and derived tables, a list of more than one table-expression results in a Cartesian product of the rows formed by the underlying table expressions, which can then be restricted via the use of the WHERE clause.

UPDATES can be performed on views only if the query specification defining the view is updatable.
row-limitation clause
The row limitation clause restricts the rows being updated to only a subset of the rows that satisfy the WHERE clause. The TOP and START AT arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The TOP argument must evaluate to a value greater than or equal to 0. The START AT argument must evaluate to a value greater than 0. An ORDER BY clause should be used to order the rows in a meaningful manner.
SET clause
Use the SET clause to set column names or variables to the specified expression.

Use the SET clause to set the column to a computed column value by using this format:
```
SET column-name = expression, ...
```
Each specified column is set to the value of the expression. There are no restrictions on expression. If expression is a column-name, then the previous value from that column is used.

If a column has a default defined, then use the SET clause to set a column to its default value.

You can also use the SET clause to assign a variable by using the following format:
```
SET @variable-name = expression, ...
```
The owner specification is only for use with database-scope variables.

When assigning a value to a variable, the variable must already be declared, and its name must begin with the at sign (@). If the variable name matches the name of a column in the table to be updated, then the UPDATE statement updates the column value and leaves the variable unchanged. Variable and column assignments can be combined in any order.
FROM clause
The FROM table-expression clause allows tables to be updated based on joins. table-expression can contain arbitrary complex table expressions, such as OUTER, CROSS, and NATURAL joins.

If the FROM clause is present, then table-name must specify the sole table to be updated, and it must qualify the name in the same way as it appears in the FROM clause. If correlation names are used in the FROM clause, then the identical correlation name must be specified as table-name. If the table expression to be updated is a derived table, then the derived table must be repeated in the table-name specification.

Updating a specified table is not allowed if the ansi_update_constraints option is set to Strict.

If a FROM clause is specified, then the SET clause can specify only columns from table-name to be updated. Otherwise, an error is generated.

The following statement illustrates a potential ambiguity in table names in UPDATE statements using the syntax for updating a specific table that use correlation names:
```
UPDATE table_1
SET column_1 = ...
FROM table_1 AS alias_1, table_1 AS alias_2
WHERE ...
```
In the following example, each instance of table_1 in the FROM clause has a correlation name, denoting a self-join of table_1 to itself. However, the UPDATE statement fails to specify which of the rows that make up the self-join are to be updated. This omission can be corrected by specifying the correlation name in the UPDATE statement as follows:
```
UPDATE table_1 as alias_1
SET column_1 = ...
FROM table_1 AS alias_1, table_1 AS alias_2
WHERE ...
```
WHERE clause
If a WHERE clause is specified, then only rows satisfying the search condition are updated. If no WHERE clause is specified, then every row is updated.
ORDER BY clause
Normally, the order in which rows are updated does not matter. However, with the FIRST or TOP clause, the order can be significant.

You cannot use ordinal column numbers in the ORDER BY clause.

To use the ORDER BY clause, the ansi_update_constraints option must not be set to Strict.

To update columns that appear in the ORDER BY clause, the ansi_update_constraints option must be set to Off.
OPTION clause
Use this clause to specify hints for executing the statement. The setting you specify is only applicable to the current statement and takes precedence over any public or temporary option settings, including those set by ODBC-enabled applications. The following hints are supported:
- MATERIALIZED VIEW OPTIMIZATION option-value
- FORCE OPTIMIZATION
- FORCE NO OPTIMIZATION
- option-name = option-value.
  
  Use an OPTION( isolation_level = ... ) specification in the query text to override all other means of specifying isolation level for a query.
  
  Use an OPTION( parameterization_level = ... ) specification in the query text to override the parameterization level for a query.

Remarks

The UPDATE statement is used to modify the rows in one or more tables. Each named column is set to the value of the expression on the right side of the equal sign. There are no restrictions on the expression. Even column-name can be used in the expression. The old value is used.

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 the string Street is always held in the database with an uppercase S and the remainder of the letters lowercase. SELECT statements return the string as Street. If the database is not case sensitive, however, then all comparisons make Street the same as street, STREET, and so on. Further, if a single-column primary key already contains an entry Street, then an UPDATE of another row's primary key to street is rejected, as it would make the primary key not unique.

If the new value does not differ from the old value, then no change is made to the data. However, BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value.

Updating a significant amount of data using the UPDATE statement also updates column statistics.

If no WHERE clause is specified, then every row is updated. If a WHERE clause is specified, then only those rows which satisfy the search condition are updated.

Normally, the order that rows are updated in does not matter. However, with the NUMBER(*) function, an ordering can be useful to get increasing numbers added to the rows in some specified order. Also, to do something like add 1 to the primary key values of a table, it is necessary to do this in descending order by primary key, so that you do not get duplicate primary keys during the operation.

Views can be updated provided the SELECT statement defining the view does not contain a GROUP BY clause, an aggregate function, or involve a UNION clause.

The optional FROM clause allows tables to be updated based on joins. If the FROM clause is present, then the WHERE clause qualifies the rows of the FROM clause. When specifying a FROM clause, qualify the table name that is being updated the same way in both parts of the statement. If a correlation name is used in one place, then use the same correlation name in the other. Otherwise, an error is generated.

When updating database-scope variables using the SET clause, the setting does not persist between restarts of the database, even though the variable does. When a database is restarted, the value of a database-scope variable reverts to NULL or its default, if defined. The SYSDATABASEVARIABLE system view contains a list of all database-scope variables and their default values.

You cannot update a database-scope variable owned by another user.

Privileges

You must be the owner of the table being updated, or have UPDATE privilege on the columns being modified, or have the UPDATE ANY TABLE system privilege.

No privileges are required to update a self-owned database-scope variable. To update a database-scope variable owned by PUBLIC, you must have the UPDATE PUBLIC DATABASE VARIABLE system privilege.

Side effects

Column statistics are updated to reflect the modified values.

If a table has a primary key, a UNIQUE constraint, or a UNIQUE index, then the processing of the UPDATE statement may involve the use of a temporary table if the table manipulations cannot be performed without violating the uniqueness constraint. The temporary table stores rows modified by the UPDATE statement that violate one or more uniqueness constraints. These rows are temporarily deleted from the base table during the execution of the UPDATE statement, and are subsequently re-inserted. This behavior may have implications for AFTER triggers and other concurrent connections.

Standards

ANSI/ISO SQL Standard
The UPDATE...table-expression syntax is a Core Feature of the ANSI/ISO SQL Standard. However the following extensions are not in the standard:
- The FROM and ORDER BY clauses.
- The row-limitation clause.
- The ability to specify more than one table-expression.
- The ability to update a variable using the SET clause.
- The OPTION clause.
The UPDATE...table-expression syntax also includes support for two optional ANSI/ISO SQL Language Features:
- Support for updating a join, possibly including one or more derived tables, comprises part of optional ANSI/ISO SQL Language Feature T111, "Updatable joins, unions, and columns".
- Support for modifying a table referenced in a nested subquery that forms part of the search condition for the UPDATE statement comprises optional ANSI/ISO Language Feature F781, "Self-referencing operations".
With the UPDATE...table-expression syntax, the setting of the ansi_update_constraints option controls which forms of table expressions can be modified. To enforce ANSI/ISO Core Feature compatibility, ensure that the ansi_update_constraints option is set to Strict.

The UPDATE...table-name syntax is not in the standard.

Example

Using the sample database, this example transfers employee Philip Chin (employee 129) from the sales department to the marketing department.

UPDATE GROUPO.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 GROUPO.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. Because the statement specifies an ORDER BY clause, and the ordering attribute is also specified in the SET clause, the ansi_update_constraints option must be set to Off or an error is returned.

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 GROUPO.Products
SET UnitPrice = 7.00
WHERE ID = 501
OPTION( isolation_level = 2 );

This example requires the ansi_update_constraints option to be set to a value other than Strict. Again using the sample database, this example uses the UPDATE...table-name syntax to reset the quantity-on-hand of those Tee Shirts where there exists at least one order whose quantity exceeds the current quantity-on-hand:

UPDATE GROUPO.Products AS a
SET Quantity = 0
FROM GROUPO.Products a JOIN GROUPO.SalesOrderItems b ON a.ID = b.ProductID
WHERE a.Name = 'Tee Shirt' AND b.Quantity > a.Quantity;

This example requires the ansi_update_constraints option to be set to a value other than Strict. In this example, the UPDATE...table-expression syntax is used to reset the quantity-on-hand for those Tee Shirts, and to reset the ShipDate for the Tee Shirt order to today's date:

UPDATE GROUPO.Products a JOIN GROUPO.SalesOrderItems b on a.ID = b.ProductID 
SET a.Quantity = 0, b.ShipDate = CAST( NOW() AS DATE)
WHERE a.Name = 'Tee Shirt' AND b.Quantity > a.Quantity

This example shows how to update a table to set a column to its default value. In this example, you create a table, MyTable, populate it with data, and then execute an UPDATE statement specifying the SET clause to update some column values.

CREATE OR REPLACE TABLE MyTable(
   PK INT PRIMARY KEY  DEFAULT AUTOINCREMENT,
   TableName CHAR(128) NOT NULL,
   TableNameLen INT DEFAULT 20,
   LastUser CHAR(10) DEFAULT LAST USER,
   LastTime TIMESTAMP DEFAULT TIMESTAMP,
   LastTimestamp TIMESTAMP DEFAULT CURRENT TIMESTAMP );

INSERT INTO MyTable WITH AUTO NAME
   SELECT
      LENGTH(t.table_name) AS TableNameLen,
      t.table_name AS TableName
   FROM SYS.SYSTAB t
   WHERE table_id <= 10;

WAITFOR DELAY '00:00:05';

UPDATE MyTable 
   SET TableName = TableName || '*',
       LastTimestamp = DEFAULT
   WHERE TableName LIKE '%idx%';

SELECT * FROM MyTable;

In this example, the LastTime column is automatically updated because its default is TIMESTAMP. Ordinarily, the LastTimestamp column would not update since its default is CURRENT TIMESTAMP. The SET statement forces an update of this column value using its default.