|
|
|
Valid in: SQL, ESQL, DBProc
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 [schema.]table_name [corr_name]
{ , [schema.]table_name [corr_name]}]
SET column_name = expression {, column_name = expression}
[WHERE search_condition];
Embedded non-cursor version:
EXEC SQL [REPEATED] UPDATE [schema.]table_name [corr_name]
[FROM [schema.]table_name [corr_name]
{ , [schema.]table_name [corr_name]}]
SET column = expression {, column = expression}
[WHERE search_condition];
Embedded cursor version:
EXEC SQL UPDATE [schema.]table_name
SET column = expression {, column = 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.
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 "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.
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, 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: