UPDATE (SQL)

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

UPDATE Usage

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 syntax structures are possible.

Syntax 1 - Searched UPDATE

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 names 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.

UPDATE

brackets

 view-name  [correlation-name]  SET *

brackets
table-name  [correlation-name] SET assignment-list
  [WHERE search-condition]

brackets

 WITH

brackets

 RR
RS
CS

brackets

brackets

    

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 DEFINE DATA statement. For further information, see view-name (in the section Basic Syntactical Items).

correlation-name
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).

SET
SET Clause:

If a view has been specified for updating, an asterisk (*) has to be specified in the SET clause, because all columns of the view must be updated.

If a table has been specified for updating, the SET clause must contain either an assignment-list or the name of the view which contains the columns to be updated.

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 WHERE clause is specified, the entire table is updated.

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 description of the SELECT statement.

It is only valid against Db2 databases. When used against other databases, it will cause runtime errors.

CS Cursor Stability
RR Repeatable Read
RS Read Stability

Assignment List

brackets

 column-name =

brackets

 scalar-expression

brackets

brackets

 ,

ellipsis
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").

Syntax Element Description:

Syntax Element Description
column-name
Column Name:

Specifies the name of a column of the result table of the MERGE statement that is not the same name as another include column or a column in the target table.

NULL
NULL Option:

Specifies the null value as the new value of the column.

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").

Syntax 2 - Positioned UPDATE

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

brackets

 view-name   SET *

brackets

 [WHERE CURRENT OF CURSOR  (r)]
view-name   SET assignment-list

Syntax Element Description - Syntax 2:

Syntax Element Description
view-name
Natural View:

Refers to the name of a Natural view as defined in the DEFINE DATA statement; see also view-name (in the section Basic Syntactical Items).

SET *

SET assignment-list
SET Clause:

If a Natural view has been specified for updating, an asterisk (*) has to be specified in the SET clause, because all columns of the view must be updated.

If a table has been specified for updating, the SET clause must contain either an assignment-list or the name of the view which contains the columns to be updated.

WHERE CURRENT OF CURSOR (r)
Statement Reference:

The (r) notation is used to reference the statement which was used to select the row to be updated. If no statement reference is specified, the UPDATE statement is related to the innermost active processing loop in which a database record was selected.

UPDATE Examples

Example 1 - Searched UPDATE

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

Example 2 - Searched UPDATE with assignment-list

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

Example 3 - Positioned UPDATE

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

Example 4 - Positioned UPDATE with assignment-list

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