The UPDATE statement updates the values of specified columns in rows of a table.
Invocation
This statement can be issued through the DB2eCLP.
Syntax
>>-UPDATE--table-name--SET--------------------------------------> .-,----------------------------------. V | >----+--------------------------------+-+-----------------------> '-column-name--=----expression---' >--+---------------------------------+------------------------->< '-WHERE----| search_condition |---' expression .-arithmetic operator----------. V | |----+----+--+-literal----------+-+-----------------------------| +-+--+ +-column-name------+ '- --' '-special register-' operator |--+-CONCAT-+---------------------------------------------------| +-/------+ +-*------+ +-+------+ '- ------' search_condition .----------------------------------------------. V | |----+------------------------------------------+-+-------------| '-+-AND-+--+-----+--+-| predicate |------+-' '-OR--' '-NOT-' '-(search_condition)-' predicate (1) |--+-| basic predicate |-+--------------------------------------| +-| IN predicate |----+ +-| LIKE predicate |--+ '-| NULL predicate |--' basic predicate |--| expression |--+-=--+--| expression |-----------------------| +-<>-+ +-<--+ +->--+ +-<=-+ +->=-+ '-||-' IN predicate |--| expression |--+-----+--IN--(--| expression |--)------------| '-NOT-' LIKE predicate |--| expression |--+-----+--LIKE--| expression |----------------| '-NOT-' NULL predicate |--| expression |--IS--+-----+--NULL----------------------------| '-NOT-' relational operator |--+-=-----------+----------------------------------------------| +-<>----------+ +-<-----------+ +->-----------+ +-<=----------+ +->=----------+ +-LIKE--------+ +-NOT LIKE----+ +-IS NULL-----+ '-IS NOT NULL-'
- BLOB expressions are allowed only in NULL predicates.
Description
- table-name
- Is the name of the table to be updated. The name must identify a table described in the catalog, but not a catalog table.
- SET
- Introduces the assignment of values to column names.
- column-name
- Identifies a column to be updated. The column-name must identify a column of the specified table. A column must not be specified more than once (SQLSTATE 42701).
- expression
- An expression can be a literal, column name, or special register.
Arithmetic operations on BLOB(n), DATE, TIME, and TIMESTAMP data types are not supported.
- literal
- A literal can be a value of data type INTEGER, SMALLINT, DECIMAL, CHAR(n), VARCHAR(n), BLOB(n), DATE, TIME, or TIMESTAMP.
- special register
- The special registers CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP can be used to produce the current date, time, and timestamp.
- WHERE
- Introduces a condition that indicates what rows are updated. You can omit the clause or give a search condition. If the clause is omitted, all rows of the table are updated.
- search_condition
- A search_condition specifies a condition that
is true, false, or unknown about a given row.
The result of a search_condition is derived by applying the specified logical operators (AND, OR, NOT) to the result of each specified predicate. A predicate compares two values. If logical operators are not specified, the result of the search condition is the result of the specified predicate.
Search conditions within parentheses are evaluated first. If the order of evaluation is not specified by parentheses, NOT is applied before AND, and AND is applied before OR. The order in which operators at the same precedence level are evaluated is undefined to allow for optimization of search conditions.
The search_condition is applied to each row of the table and the updated rows are those for which the result of the search_condition is true.
Each column-name in the search condition must identify a column of the table.
You can use the CONCAT, MOD, LENGTH, and RTRIM functions in the predicate expression of the search condition. For more information about the MOD function, see SELECT.
- NOT
- If NOT is specified, the result of the predicate is reversed.
- relational operator
- Can be any of the following operators:
- =
- Equal to.
- <>
- Not equal to.
- <
- Less than.
- >
- Greater than.
- <=
- Less than or equal to.
- >=
- Greater than or equal to.
- LIKE
- Matches one character string. Use a single-byte character-set (SBCS) underscore to refer to one SBCS character. Use a double-byte character-set (DBCS) underscore to refer to one DBCS character. For example, the condition WHERE PART_NUMBER LIKE '_0' returns all 2–digit part numbers ending in 0 (20, 30, and 40, for example). Use a percent (either SBCS or DBCS) to refer to a string of zero or more SBCS or DBCS characters. For example, the condition WHERE DEPT_NUMBER LIKE '2%' returns all department numbers beginning with the number 2 (20, 27, or 234, for example).
- NOT LIKE
- Does not have at least one of the same characters.
- IS NULL
- Contains the null value.
- IS NOT NULL
- Does not contain the null value.
- AND
- If specified, the logical operator AND is applied to the result of each specified predicate.
- OR
- If specified, the logical operator OR is applied to the result of each specified predicate.
Rules
- Assignment: Update values are assigned to columns under the assignment rules described in the DB2® Version 9.1.
- UPDATE never applies to logically deleted records.
- You cannot update values into an Oracle ROWID column. If you attempt to update values in this type of column, DB2 Everyplace® returns SQLSTATE 428C9.
Notes
- In system mode the dirty bit is set by default. If you are running your application in system mode (SQL_DIRTYBIT_SET_BY_SYSTEM), you cannot manually set the dirty bit. If you try to set the dirty bit, an error will occur.
Example
UPDATE EMPLOYEE SET PHONENO = '1234' WHERE EMPNO = '003002'
