Updates a set of rows in a table or view.
The table or view identified by the table name is updated in the rows which satisfy the condition in the
WHEREclause by assigning new values to the columns as specified in the
SETclause. If no
WHEREclause is specified, all rows are updated.
Values to be assigned to columns may be specified either as expressions or by using the keywords
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
UPDATEclause. 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 FOUNDcondition code is returned, see Return Status and Conditions.
expression, see Expressions.
search-condition, see Search Conditions.
with-clause, see The WITH Clause.
UPDATEaccess is required on the columns specified in the
UPDATEstatement is used on a primary key column of a table, the table must be stored in a databank with the
In a procedural usage context, the
UPDATEstatement is only permitted if the procedure
MODIFIES SQL DATA, see CREATE PROCEDURE.
Column names on the left-hand side of the assignment operator in the
SETclause 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
SETclause in a single
Expressions used in the
SETclause cannot refer to set functions (except for in a subquery).
Column names in the search condition of the
WHEREclause must identify columns in the table or view to be updated.
If a correlation name is introduced after the table reference in the
UPDATEclause, the correlation name must be used to refer to the table in the
WHEREclause of the same
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
UPDATEstatement have been made).
If the table name specified in the
UPDATEstatement is subject to any referential constraint, the values in all updated rows must conform to that constraint. If a view defined
WITH CHECK OPTIONis 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.
UPDATEstatement 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
WORKoption it is possible that some rows will be updated).
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';
Mimer Information Technology AB
Phone: +46 18 780 92 00