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:
NDB - UPDATE - SQL in the Natural for DB2 part.
UPDATE 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
(see the System Variables documentation).
UPDATE
|
|
view-name [correlation-name]
SET *
|
|
||||||||||
table-name [correlation-name]
SET
assignment-list
|
|||||||||||||
[WHERE
search-condition]
|
|
WITH
|
|
RR RS CS |
|
|
[QUERY NO integer]
|
||||||
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Syntax Element Description - Syntax 1:
| view-name |
View Name:
Refers to the name of a Natural view as defined in 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 | 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 |
|
|
column-name = |
|
scalar-expression
NULL
|
|
|
|
In an assignment-list, you can assign values
to one or more columns. A value can be either a
scalar-expression 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").
The following syntax elements belong to the SQL Extended Set:
| correlation-name | The item correlation-name represents an alias name for a table-name. For further information, see correlation-name (in the section Basic Syntactical Items). |
|---|---|
| WITH |
WITH Isolation Level Clause:
This clause allows the explicit specification of the isolation level used when locating the row to be updated. For detailed information, see WITH - Isolation Level in the corresponding section NDB - SELECT - Cursor-Oriented (in the Natural for DB2 part of the Database Management System Interfaces documentation). |
| QUERYNO integer |
QUERYNO Clause:
This clause allows you to explicitly specify the number to
be used in |
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:
| view-name |
View Name:
Refers to the name of a Natural view as defined in the
|
|---|---|
| SET *
SET assignment-list |
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 |
| 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 assignment-list. This clause cannot be specified if
view-name
|
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 ...
