UPDATE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The UPDATE statement updates column values in a table.
The UPDATE statement has the following format:
Interactive version:
UPDATE [schema.]table_name [corr_name]
[FROM from_source {, from_source}]
SET [corr_name.]column_name = expression{,
[corr_name.]column_name = expression}
[WHERE search_condition];
Embedded non-cursor version:
EXEC SQL [REPEATED] UPDATE [schema.]table_name [corr_name]{ ,
[FROM from_source {, from_source}]
SET [corr_name.]column_name = expression{,
[corr_name.]column_name = expression}
[WHERE search_condition];
Embedded cursor version:
EXEC SQL UPDATE [schema.]table_name
SET [corr_name.]column_name = expression{,
[corr_name.]column_name = expression}
WHERE CURRENT OF cursor_name;
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.” If a row update violates an integrity constraint on the table, the update is not performed. For details about integrity constraints, see
CREATE TABLE.
The 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.”
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” 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;
Last modified date: 04/03/2024