UPDATE (SQL)

#### Search Results

Search

# UPDATE (SQL)

This document covers the following topics:

- UPDATE Usage

- Syntax 1 - Searched UPDATE

- Syntax 2 - Positioned UPDATE

- UPDATE Examples

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` |  | view-name [correlation-name] `SET *` |
table-name [correlation-name] `SET` assignment-list
[`WHERE` search-condition] |  | `WITH` |  | RR RS CS |  |  |  |

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

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

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` |  | view-name `SET *` |  | [`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

- Example 2 - Searched UPDATE with assignment-list

- Example 3 - Positioned UPDATE

- Example 4 - Positioned UPDATE with assignment-list

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

### On this page

- UPDATE Usage
- Syntax 1 - Searched UPDATE
- Syntax 2 - Positioned UPDATE
- UPDATE Examples