UPDATE (SQL)

This document covers the following topics:

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

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

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