Description
The UPDATE statement replaces the values of the specified columns by the values of the specified expressions for all rows of the table that satisfy the search_condition. For a discussion of search conditions, see the chapter
Elements of OpenSQL Statements (see page
OpenSQL Statements). If a row update violates an integrity constraint on the table, the update is not performed. For details about integrity constraints, see
Create Table. (see page
CREATE TABLE)
table_name specifies the table for which the constraint is defined. A correlation name (corr_name) can be specified for the table for use in the search_condition. For a definition of correlation names and discussion of their use, see the chapter
Overview of OpenSQL (see page
OpenSQL Statements).
The expressions in the SET clause can use constants or column values from the table being updated or any tables listed in the FROM clause. They also can be a scalar subquery (a SELECT statement that returns 0 or 1 rows and has a single entry in the select list).
If a column name specifies a numeric column, its associated expression must evaluate to a numeric value. Similarly, if a column name represents a character type, its associated expression must evaluate to a character type.
The result of a correlated aggregate cannot be assigned to a column. For example, the following UPDATE statement is invalid:
UPDATE mytable FROM yourtable
SET mytable.mycolumn = MAX(yourtable.yourcolumn);
To assign a null to a nullable column, use the null constant.
Note: To update long varchar or long byte columns, specify a DATAHANDLER clause in place of the host language variable in the SET clause. For details about data handler routines, see the chapter
Embedded OpenSQL (see page
OpenSQL Statements) and the
Embedded SQL Companion Guide. The syntax for the DATAHANDLER clause is as follows:
DATAHANDLER(handler_routine ([handler_arg]))[:indicator_var]
Note: If II_DECIMAL is set to comma (,), be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM t1;