UPDATE

The UPDATE statement updates the values of specified columns in rows of a table.

Invocation

This statement can be used in an application program using the DB2 CLI functions or issued through the CLP.

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-'

Poznámky:

  1. 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 page ***.

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

Notes

Example

Change the phone number (PHONENO) of employee number (EMPNO) '003002' in the EMPLOYEE table to '1234'. 

UPDATE EMPLOYEE
 SET PHONENO = '1234'
 WHERE EMPNO = '003002'

Související odkazy