This document covers the following topics:
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Belongs to Function Group: Database Access and Update
See also the following sections in the Database Management System Interfaces documentation:
UPDATE - SQL in the Natural for DB2 part.
UPDATE - SQL in the Natural SQL Gateway part.
UPDATE - SQL in the Natural for SQL/DS part.
The SQL UPDATE
statement is used to perform an
UPDATE
operation on either rows in a table without using a cursor
("searched"
UPDATE
) or columns in a row to which a cursor is
positioned ("positioned"
UPDATE
).
Two different structures are possible.
The "Searched" UPDATE
statement is a
stand-alone statement not related to any SELECT
statement. With a single
statement you can update zero, one, multiple or all rows of a table. The rows
to be updated are determined by a
search-condition
that is applied to the
table. Optionally, view and table names can be assigned a
correlation-name
.
Note:
The number of rows that have actually been updated with a
"searched" UPDATE
can be ascertained by using the
system variable *ROWCOUNT
.
|
||||||||||||||||||||||
|
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Syntax Element Description - Syntax 1:
Syntax Element | Description | |
---|---|---|
view-name
|
View Name:
Refers to the name of a Natural view as defined in the
|
|
correlation-name
|
Correlation Name:
The item
For further information, see |
|
include-columns |
Include Columns Clause:
Specifies a set of columns that are included, along with the
columns of tables-name in the result table of the |
|
SET
|
SET Clause:
If a view has been specified for updating, an
asterisk (*) has to be specified in the If a table has been specified for updating, the |
|
assignment-list
|
Assignment List: See
Assignment List below.
|
|
WHERE
search-condition
|
WHERE Clause:
This clause is used to specify the selection criteria for the rows to be updated. If no |
|
WITH
|
WITH Clause:
This clause allows the explicit specification of the isolation
level used when locating the row to be updated. For detailed information, see WITH Clause - Isolation Level in the description of the |
|
CS |
Cursor Stability | |
RR |
Repeatable Read | |
RS |
Read Stability | |
SKIP LOCKED DATA |
SKIP LOCKED DATA Clause:
Specifies that rows are skipped when incompatible locks are held on the row by other transactions. |
|
QUERYNO
integer
|
QUERYNO Clause:
This clause allows you to explicitly specify the number to
be used in |
column-name = | scalar-expression | , | |||||
DEFAULT |
|||||||
NULL |
In an assignment-list
, you can
assign values to one or more columns. A value can be a
scalar-expression
, DEFAULT
or NULL
.
For further information, see Scalar
Expressions.
If the value NULL
has been assigned, it means that the addressed field is to contain no value (not even the value "0" or "blank").
Alternative:
(column-name,...) =( | scalar-expression | , | ) | ||||||||
DEFAULT |
|||||||||||
NULL |
|||||||||||
row-fullselect |
Syntax Element Description:
The "positioned" UPDATE
statement always
refers to a cursor within a database loop. Thus, the table or view referenced
by a positioned UPDATE
statement must be the same as the one
referenced by the corresponding SELECT
statement; otherwise an
error message is returned. A positioned UPDATE
cannot be used with
a non-cursor selection.
Common Set Syntax:
UPDATE
|
view-name
SET *
|
[WHERE CURRENT OF CURSOR
(r)]
|
||
view-name
SET
assignment-list
|
Extended Set Syntax:
UPDATE
|
view-name
SET *
|
[WHERE CURRENT OF CURSOR
(r)
|
FOR ROW |
[:]host-variable | OF ROWSET |
||||||
view-name
SET
assignment-list
|
integer |
Syntax Element Description - Syntax 2:
Syntax Element | Description |
---|---|
view-name
|
Natural View:
Refers to the name of a Natural view as defined in the
|
SET *
|
SET Clause:
If a Natural view has been specified for updating, an asterisk (*) has to
be specified in the If a table has been specified for updating, the |
WHERE CURRENT OF
CURSOR (r) |
Statement Reference:
The |
FOR ROW … OF
ROWSET |
FOR ROW … OF ROWSET Clause:
This clause belongs to the SQL Extended Set. The optional If this clause is omitted, all rows of the current rowset are
updated by the values in the This clause cannot be specified if
|
DEFINE DATA LOCAL 1 PERS VIEW OF SQL-PERSONNEL 2 NAME 2 AGE ... END-DEFINE ... ASSIGN AGE = 45 ASSIGN NAME = 'SCHMIDT' UPDATE PERS SET * WHERE NAME = 'SCHMIDT' ...
DEFINE DATA LOCAL 1 PERS VIEW OF SQL-PERSONNEL 2 NAME 2 AGE ... END-DEFINE ... UPDATE SQL-PERSONNEL SET AGE = AGE + 1 WHERE NAME = 'SCHMIDT' ...
DEFINE DATA LOCAL 1 PERS VIEW OF SQL-PERSONNEL 2 NAME 2 AGE ... END-DEFINE ... SELECT * INTO PERS FROM SQL_PERSONNEL WHERE NAME = 'SCHMIDT' COMPUTE AGE = AGE + 1 UPDATE PERS SET * WHERE CURRENT OF CURSOR END-SELECT ...
DEFINE DATA LOCAL 1 PERS VIEW OF SQL-PERSONNEL 2 NAME 2 AGE ... END-DEFINE ... SELECT * INTO PERS FROM SQL-PERSONNEL WHERE NAME = 'SCHMIDT' UPDATE SQL-PERSONNEL SET AGE = AGE + 1 WHERE CURRENT OF CURSOR END-SELECT ...