UPDATE

---

Updates a set of rows in a table or view.

Usage

Embedded/Interactive/Procedural/ODBC/JDBC.

Description

The table or view identified by the table name is updated in the rows which satisfy the condition in the WHERE clause by assigning new values to the columns as specified in the SET clause. If no WHERE clause is specified, all rows are updated.

Values to be assigned to columns may be specified either as expressions or by using the keywords NULL or DEFAULT. Expressions must have a data type compatible with the definition of the column to which they are assigned. If column names are used in expressions, they must refer to columns in the table or view addressed in the UPDATE clause. The value specified by a column name in an expression is the value for the column in the row concerned before any update operation is performed.

If no row is updated a NOT FOUND condition code is returned, see Return Status and Conditions.

Language Elements

expression, see Expressions.

search-condition, see Search Conditions.

Restrictions

UPDATE access is required on the columns specified in the SET clause.

If the UPDATE statement is used on a primary key column of a table, the table must be stored in a databank with the TRANSACTION or LOG option.

In a procedural usage context, the UPDATE statement is only permitted if the procedure access-clause is MODIFIES SQL DATA, see CREATE PROCEDURE.

Notes

Column names on the left-hand side of the assignment operator in the SET clause may not be qualified by the table reference.

Columns may not be specified more than once on the left-hand side of the assignment operator in the SET clause in a single UPDATE statement.

Expressions used in the SET clause cannot refer to set functions (except for in a sub-query).

Column names in the search condition of the WHERE clause must identify columns in the table or view to be updated.

If a correlation name is introduced after the table reference in the UPDATE clause, the correlation name must be used to refer to the table in the WHERE clause of the same UPDATE statement.

UNIQUE and CHECK constraints in the table being updated may not be violated (this is evaluated at the end when all the modifications involved in the UPDATE statement have been made).

If the table name specified in the UPDATE statement is subject to any referential constraint, the values in all updated rows must conform to that constraint. If a view defined WITH CHECK OPTION is to be updated, the values assigned to the columns must conform to the view definition.

Read-only views may not be updated, see CREATE VIEW.

An UPDATE statement is executed as a single statement. If an error occurs at any point during the execution, no rows will be updated (however, if the table is stored in a databank with the WORK option it is possible that some rows will be updated).

Example

The following example is taken from the Mimer SQL User's Manual, Updating Tables.

 UPDATE currencies SET exchange_rate = 7.25 WHERE currency_code = 'USD';

Multiple column update example:

 UPDATE currencies SET exchange_rate = 36.38, currency = 'Jaimacan Dollars'
   WHERE currency_code = 'JMD';
 

can also be written as:

 UPDATE currencies SET (exchange_rate, currency) = (36.38, 'Jaimacan Dollars')
   WHERE currency_code = 'JMD';

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Core	Fully compliant.
SQL-2011	Features outside core	Feature F781, "Self-referencing operations" the update table can be used in search conditions in the update statement.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se