4.6.1 Specification format and rules for the UPDATE statement

The UPDATE statement updates values in a row.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

■ To update rows by specifying the names of the columns to update:

UPDATE-statement ::=
     UPDATE table-name [[AS] correlation-name]
       SET update-target-column-name=update-value[, update-target-column-name=update-value]...
          [WHERE search-condition]
 
 update-value ::=  {value-expression | NULL | DEFAULT}

■ To update an entire row by specifying ROW:

UPDATE-statement ::=
     UPDATE table-name [[AS] correlation-name]
       SET ROW=row-update-value
          [WHERE search-condition]
 
row-update-value ::=  dynamic-parameter

To Page Top

(2) Explanation of specification format

table-name

Specifies the name of the table to be updated (the update target table). For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that you cannot specify a read-only viewed table.

[AS] correlation-name

Specifies the correlation name of the update target table. For details about correlation names, see (4) Table specification format in 6.1.5 Qualifying a name. For details about the effective scope of correlation names, see 6.8 Scope variables.

update-target-column-name=update-value[, update-target-column-name=update-value]...

update-value ::= {value-expression | NULL | DEFAULT}

Specifies the columns to be updated and their update values (the values after the update).

update-target-column-name can be specified in the form of a column specification. For details about column specifications, see (5) Column specification format in 6.1.5 Qualifying a name.

Specify one of the following for update-value.

value-expression:

Specify the post-update value in the form of a value expression. For details about value expressions, see 7.20 Value expression.

NULL:

Specify this to set the post-update value to the null value.

DEFAULT:

Specify this to set the post-update value to the default value for the column specified in the DEFAULT clause of the CREATE TABLE statement. For details about the default values of columns, see 7.10 DEFAULT clause.

If no default value is specified in a DEFAULT clause for a column, the null value is assumed as the default value for the column.

WHERE search-condition

Specifies the conditions for selecting the rows to update. If the WHERE clause is omitted, all the rows in the specified table are updated.

For details about search conditions, see 7.18 Search conditions.

The following rules apply:

You can specify dynamic parameters in the search conditions.

If you specify an updatable viewed table in table-name, note the following points:

When you update rows in an updatable viewed table, it updates the rows in the underlying table.
The rows of the underlying table that will be updated are those that satisfy both the search conditions specified here and the search conditions specified when the viewed table was defined.
If the WHERE clause is omitted, the rows of the underlying table that will be updated are those that satisfy the search conditions specified when the viewed table was defined.

ROW=row-update-value

row-update-value ::= dynamic-parameter

Specified to insert data by row. ROW can be specified only for FIX tables. When you specify ROW, the entire row is updated as one item of data.

The assumed data type of the dynamic parameter is the CHAR type. The data length is the row length of the table being updated. Align the boundaries so that there are no gaps in the structure. For details about how to calculate the row length, see the ROWSZ calculation formula in Determining the number of pages for storing each type of row in the HADB Setup and Operation Guide.

Note that only one dynamic parameter can be specified.

To Page Top

(3) Privileges required at execution

To execute the UPDATE statement, all of the following privileges are required:

The CONNECT privilege
UPDATE privilege for a table whose rows are to be updated
SELECT privilege for a table specified in a query expression body

Example

UPDATE "T1"
    SET "C1"='P001'
       WHERE "T1"."C2" IN (SELECT "C2" FROM "T2" WHERE "C3"<=100)

The UPDATE privilege for Table T1 and the SELECT privilege for Table T2 are required to execute the above UPDATE statement.

To Page Top

(4) Rules

The total number of tables, derived tables, and table function derived tables specified in the UPDATE statement cannot exceed 2,048. For rules and examples of how to count the number of tables, derived tables, and table function derived tables specified in an SQL statement, see (4) Rules in 4.4.1 Specification format and rules for the SELECT statement.
If the set operations specified in the UPDATE statement are all UNION, a maximum of 1,023 set operations can be specified. However, if the specified set operations include EXCEPT or INTERSECT, no more than 63 set operations can be specified.
A maximum of 63 outer joins (FULL OUTER JOIN) can be specified in the UPDATE statement.
You cannot specify the update target table in the FROM clause of a subquery in the search conditions or update values.
For the data types of the update values, use the data types of the columns to be updated or data types that can be converted and assigned to the columns' data types. For details about data types that can be converted or assigned, see 6.2.2 Data types that can be converted, assigned, and compared.
If you specify a dynamic parameter as a row update value, the assumed data type and data length will be the data type and data length of the column to be updated.
If you update DECIMAL or DOUBLE PRECISION type data in a column defined as any of the data types listed below, the fractional (decimal) part will be truncated:
- INTEGER
- SMALLINT
Furthermore, if you use DECIMAL type data to update a DECIMAL type column, any digits beyond the scaling specified for the column will be truncated.

If you use DOUBLE PRECISION type data to update a DECIMAL type column, any digits beyond the scaling specified for the column will be rounded off (to the nearest even number).
When updating a CHAR type, VARCHAR type, BINARY type or VARBINARY type column, if the data length of the update value is greater than the defined size of the column, the table cannot be updated.
When updating a CHAR type column, if the data length of the update value is shorter than the defined size of the column, the data is stored left-aligned and trailing spaces are added.
When updating a BINARY type column, if the data length of the update value is shorter than the defined size of the column, the data is stored left-aligned in the column and the rest of the field is set to X'00'.
When updating an INTEGER, SMALLINT, or DECIMAL type column, if the update value is outside the numeric range of the data type, the table cannot be updated.
A maximum of 1,000 update target column names can be specified in the SET clause.
If there are no rows to be updated, SQLCODE is set to 100.
Each column name must be unique among the columns to be updated.
When the ROW specification is used, you cannot specify more than one SET clause.
This statement cannot be used to update rows of a dictionary table or system table.
The same operation or design that can be used when the UPDATE statement is run for row store tables cannot be used when the statement is run for column store tables. For details, see Criteria for selecting row store tables and column store tables, Checking whether a single-chunk table needs to be reorganized, and Checking whether a multi-chunk table needs to be reorganized in the HADB Setup and Operation Guide.
Archived rows cannot be updated. The UPDATE statement that is run to update archived rows will result in an error. To update archived rows, first, unarchive the chunk that stores the rows to be updated. Then, run the UPDATE statement to update the rows.
The UPDATE statement can update unarchived rows. Note, however, that the UPDATE statement you run must meet all of the following conditions:
- The archive range column is specified in a search condition.
- In the search condition in which the archive range column is specified, only a comparison predicate, the IN predicate, or the BETWEEN predicate is specified.
- OR, NOT, and other logical operators are not used in the search condition in which the archive range column is specified.
- Archived rows are not specified as the update-target rows.
Unless all of the preceding conditions are met, the UPDATE statement will result in an error.

Important

In the search condition in which the archive range column is specified, the predicates that can be specified are limited. Even if logical operators such as OR and NOT are not specified in the search condition, the UPDATE statement might result in an error. For details, see Using the datetime information of the archive range column to narrow the search range in the HADB Application Development Guide.

The following shows typical examples in which the UPDATE statement can be run and cannot be run. Note that in the following examples, table ARCHIVE-T1 is an archivable multi-chunk table, and column RECORD-DAY is the archive range column.

Example in which the UPDATE statement can be run

Example:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" BETWEEN DATE'2016/02/01' AND DATE'2016/02/29'
      AND "CODE"='P001'
```
In the preceding example, the UPDATE statement can be run because all of the following conditions are met:
- The archive range column (RECORD-DAY) is specified in a search condition.
- Only the BETWEEN predicate is specified in the search condition in which the archive range column is specified.
- OR, NOT, and other logical operators are not used in the search condition in which the archive range column is specified.
- Archived rows are not specified as the update-target rows.
  
  Important
  
  For the comparison with the archive range column specified in a search condition, we recommend that you specify a literal.
  
  Example of recommended specification:
  
  "RECORD-DAY" BETWEEN DATE'2016/01/01' AND DATE'2016/01/10'
  
  "RECORD-DAY" >= DATE'2016/02/10'
  
  We recommend that you specify only a literal.
  
  Example of specification that is not recommended:
  
  "RECORD-DAY" BETWEEN ? AND ?
  
  "RECORD-DAY" >= CURRENT_DATE
  
  Note
  
  The HADB server determines whether the update-target data has been archived from the search condition in which the archive range column is specified. If you specify a literal as the comparison with the archive range column, you can reduce the time required for determination. If you do not specify a literal, determination might require a very long time.
Examples in which the UPDATE statement cannot be run

• No archive range column is specified in search conditions

Example 1:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100      
```
In this example, because the archive range column (RECORD-DAY) is not specified in the search condition, the UPDATE statement results in an error.

Example 2:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "CODE"='P001'
```
In this example, because the archive range column (RECORD-DAY) is not specified in the search condition, the UPDATE statement results in an error. An error occurs even when an attempt is made to update unarchived rows.

• Logical operations such as OR and NOT are specified in the search condition in which the archive range column is specified

Example:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" BETWEEN DATE'2016-01-01' AND DATE'2016-01-31'
       OR "RECORD-DAY" BETWEEN DATE'2016-03-01' AND DATE'2016-03-31'
```
In this example, because the OR operator is specified in the search condition in which the archive range column is specified, the UPDATE statement results in an error. The preceding statement will also result in an error when an attempt is made to update unarchived rows.

In this case, you can update the rows by running the UPDATE statement twice as follows:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" BETWEEN DATE'2016-01-01' AND DATE'2016-01-31'
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" BETWEEN DATE'2016-03-01' AND DATE'2016-03-31'
```
• Archived rows are specified as the update-target rows

Example:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" BETWEEN DATE'2015/11/01' AND DATE'2016/01/31'
```
In this example, the UPDATE statement results in an error because an attempt is made to update archived rows.

• The archive range column is specified together with other items

Example:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" - 10 DAY > DATE'2016/02/01'
```
In this example, the UPDATE statement results in an error because a datetime operation using the archive range column is specified.

• A datetime operation is used in the comparison with the archive range column

Example:
```
UPDATE "ARCHIVE-T1" SET "NUMBER"=100
    WHERE "RECORD-DAY" >= CURRENT_DATE - 1 MONTH
```
In this example, the UPDATE statement results in an error because a datetime operation is used in the comparison with the archive range column.
If an archivable multi-chunk table is specified in the UPDATE statement, accesses to the location table and system table (STATUS_CHUNKS) occur. At this time, locked resources are secured for the system table (STATUS_CHUNKS). For details about locks, see Locking in the HADB Setup and Operation Guide.

To Page Top

(5) Examples

Example 1: Update rows by specifying the name of the column to be updated

In the sales history table (SALESLIST), update the quantity purchased (PUR-NUM) to 6 in rows that satisfy the following conditions:

Customer ID (USERID): U00358
Product code (PUR-CODE): P003
Date of purchase (PUR-DATE): 2011-09-08

UPDATE "SALESLIST"
      SET "PUR-NUM"=6
      WHERE "USERID"='U00358'
        AND "PUR-CODE"='P003'
        AND "PUR-DATE"=DATE'2011-09-08'

Example 2: Update rows by specifying the name of the column to be updated (specifying a subquery for the update values)

Update the product color (PUR-COL) of the product whose product code (PUR-CODE) column's value is P003 in the sales history table (SALESLIST) so that it is the same color as the product whose product code (PUR-CODE) column's value is P003 in the product table (PRODUCTLIST).

UPDATE "SALESLIST"
    SET "PUR-COL" = (SELECT "PUR-COL" FROM "PRODUCTLIST" WHERE "PUR-CODE"='P003')
    WHERE "PUR-CODE"='P003'

Example 3: Update rows by ROW specification

Update the sales information in the sales history table (SALESLIST) (update the entire row using the ROW specification). The sales history table comprises the columns customer ID (USERID), product code (PUR-CODE), quantity purchased (PUR-NUM), and date of purchase (PUR-DATE).

UPDATE "SALESLIST"
    SET ROW=?
    WHERE "USERID"=?

To Page Top