UPDATE
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
The UPDATE statement updates column values in a table.
The UPDATE statement has the following format:
Interactive version:
UPDATE [schema.]tablename [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.]tablename [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.]tablename
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 “Understanding the Elements of SQL 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.
If a row update violates an integrity constraint on the table, the update is not performed. tablename 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 “Introducing SQL.”
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 “Working with Embedded SQL” 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, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;
Last modified date: 01/30/2023