UPDATE (SQL)

#### Search Results

Search

# UPDATE (SQL)

This document covers the following topics:

- Function

- Syntax 1 - Searched UPDATE

- Syntax 2 - Positioned UPDATE

- Examples

For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.

Belongs to Function Group: Database Access and Update

See also UPDATE - SQL in the Natural for Db2 part of the Database Management System Interfaces documentation:

## Function

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 [period-clause] [correlation-name] `SET *` |  |  |  |  |  |
table-name [period-clause] [correlation-name] [include-columns] `SET` assignment-list
[`WHERE` search-condition] |  | `WITH` |  | `RR` `RS` `CS` |  |  | [`SKIP LOCKED DATA`] [`QUERYNO` integer] |  |

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).
`period-clause` | Period Clause: Specifies that a period clause applies to the target of the update operation. For further information, see Period Clause 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).
`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 `UPDATE` statement, when it is nested in the `FROM` clause of a `SELECT` statement. The included columns are appended to the end of the list of columns. For further details, see include-columns.
`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.
`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 `EXPLAIN` output and trace records for this statement. The number is used as `QUERYNO` column in the `PLAN_TABLE` for the rows that contain information on this statement.

### Assignment List

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

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.
`DEFAULT` | DEFAULT Option: Specifies that the default value is used based on how the corresponding column is defined in the 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").
`row-fullselect` | Row Full Select Option: Specifies a full select that returns a single row. The column values are assigned to the corresponding column-names.

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

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 `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.
`FOR ROW … OF ROWSET` | FOR ROW … OF ROWSET Clause: This clause belongs to the SQL Extended Set. The optional `FOR ROW … OF ROWSET` clause for positioned SQL `UPDATE` statements specifies which row of the current rowset has to be updated. It should only be specified if the `UPDATE` statement is related to a `SELECT` statement, which uses rowset positioning and which has column arrays in the `INTO` clause (see into-clause). 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` `SET *` is specified.

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

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