UPDATE statement Format 3, Format 4 (Update row using an array)

Function

Multiple update operations can be performed by specifying embedded variables in an array format.

Format 3
In a given table, updates the values of rows meeting specified search conditions multiple times by column.
Format 4
In a table with a FIX specification, updates the values of rows meeting specified search conditions multiple times by column.

Privileges

A user who has the UPDATE privilege for a table can update the column values in that table.

However, if a subquery is specified in the search condition, the user needs the SELECT privilege for the table for which the subquery is specified.

Format 3: Specifying an embedded variable array to update rows in a table multiple times by column

FOR : embedded-variable
UPDATE [authorization-identifier.]table-identifier
      [IN (RDAREA-name-specification)][[AS] correlation-name]
      [used-index-SQL-optimization-specification]
      SET {update-object = update-value
          |(update-object, update-object[, update-object]) = row-subquery}
          [, {update-object = update-value
             |(update-object, update-object[, update-object]...) = row-subquery}]...
[WHERE search-condition]
[WITH ROLLBACK]

update-object:: = {column-name|component-specification | column-name [{subscript|*}]}

Format 4: Specifying an embedded variable array to update rows in a table with a FIX specification multiple times by row

FOR :embedded-variable
UPDATE [authorization-identifier.]table-identifier
      [IN (RDAREA-name-specification)][ [AS] correlation-name]
      [used-index-SQL-optimization-specification]
      SET ROW = row-update-value
     [WHERE search-condition]
     [WITH ROLLBACK]

Operands

See Format 1 for details about the operands and operand rules other than FOR, IN (RDAREA-name-specification), the SET clause, and search conditions.
Format 3 operands and operand rules

Updates the value of a column.

update-value
The following items can be specified in update-value as a column value in update value:
  • : embedded-variable-array [: indicator-variable-array]
  • Column name
  • Literal
  • Value expressions (including arithmetic and concatenation operations)
  • Scalar subquery
  • USER
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP[(p)]
  • NULL (represents the null value)
  • DEFAULT (represents the default for the column to be updated)
  • ? parameter
Embedded variables not in the array format cannot be specified in update-value.
Format 4 operands and operand rules
Specifying the ROW operand is subject to the following rules:
  1. Updating by row can be used only on base tables of the FIX attribute. ROW refers to an entire row. Specifying ROW causes HiRDB to treat the entire row as a single data item and update it with data from one area. The data type of the data to be updated should be the ROW type, irrespective of the data types of columns. For the ROW type, a variable corresponding to CHAR (n) [where n is the row length] or a structure of the same length can be specified, provided that the structure does not contain a boundary alignment gap. The data length should be equal to the row length (the sum of the data lengths of the columns).
  2. The platform on which the UAP runs and the platform on which the HiRDB server runs should have the same endian. The ROW option cannot be used between different endians. For example, if ROW is used in a Windows edition UAP, the HiRDB server should also use the Windows edition of the same endian.

Updates the value of a column.

ROW
Specify this option when updating data by row.
row-update-value
The following items can be specified as row update values corresponding to ROW:
: embedded-variable-array [: indicator-variable-array]
Operands and operand rules common to Formats 3 and 4

Specifies the embedded variable in which the number of times update operations are performed using an embedded variable array is assigned. An embedded variable of the SMALLINT type should be specified. The allowable range is from 1 to 4,096, no greater than the number of elements in the embedded variable array or in the indicator variable array. Zero and negative values are not allowed. An out-of-range value can produce a run-time error.

embedded-variable-array
This is the embedded variable declared in the array format. Specify an array variable to specify an update value other than the NULL value. Values to be used for updating rows should be assigned to the elements of the variable array. If a value to be used for updating contains the NULL value, both embedded-variable-array and indicator-variable-array should be specified.
indicator-variable-array
This is the indicator variable declared in the array format. Values indicating whether the values of the elements in embedded-variable-array are the NULL value should be assigned to the corresponding elements in indicator-variable-array. For values that can be assigned, see 1.6.5 Setting a value for an indicator variable.

If RDAREA-name-specification is for an embedded variable, it must be an embedded variable array. Specifying an embedded variable that is not an array causes an error. See Format 1 for details about other operand rules.

The default is to update all rows in the specified table.

search-condition
Specifies the criteria by which the rows to be updated are selected. All rows that meet the search condition are updated. If an embedded variable is used in search-condition, an embedded variable not in the array format cannot be specified.

Common rules

Format 3 rules
  1. The data type of embedded-variable-array should be the data type of the corresponding column or a convertible data type.
  2. The UPDATE statement using an array cannot handle the BLOB type, the BINARY type with a maximum length of 32,001 bytes or greater, or the abstract data type.
  3. The UPDATE statement using an array cannot update more than one element in a repetition column.
  4. When a column of INTEGER or SMALLINT data type is updated with fixed-point or floating-point data, any fractional part (below the decimal point) is truncated before updating. Also, when fixed-point data is inserted into a DECIMAL type column, any digits below the scaling for the column are truncated.
  5. Character data longer than the length that was specified when the table was defined or BINARY data cannot be entered as a value of the column to be updated.
  6. Numeric data outside the range of the definition area cannot be entered as a value of the column to be updated.
  7. If the data used to update a column of a fixed-length character string (including national character strings and mixed character strings) is shorter than the column length, the data is inserted left-justified, and the remainder of the column is blank-filled.
  8. The SET clause can be specified only once per SQL statement.
  9. A maximum of 30,000 items can be specified in a SET clause.
  10. The UPDATE statement cannot be executed on falsification-prevented tables. Updatable columns, however, can be updated.
    The following table indicates the updatability of column values when UPDATE ONLY FROM NULL is specified for a falsification-prevented table:
    Column value before updateColumn value after updateUpdatability
    Null valueNull valueY
    Null valueNon-null valueY
    Non-null valueNull valueN
    Non-null valueNon-null value#N
    Legend:
    Y: Updatable.
    N: Not updatable.
    Note
    For a repetition column, only updating by column from the null value (a value for which the number of elements is 0) without a subscript specification.
    In a falsification-prevented table, an error may result if, of the rows that meet specified search conditions, the column for which UPDATE ONLY FROM NULL contains rows with non-null values.
    #: Includes the same value as the pre-update value.
  11. An error occurs if WITHOUT ROLLBACK is specified for the table containing the columns to be updated and an index is defined for the column to be updated.
  12. If the index constituent column of a table with a WITHOUT ROLLBACK specification is the update object column, updating cannot be executed if the update value for the index constituent column is other than the pre-update value. For details, see the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table) in Chapter 3.
  13. If the table to be updated is a shared table and if an index is defined for the column to be updated, the LOCK statement should be executed in the lock mode on the shared table before updating. An attempt to update without executing the LOCK statement the value of a column for which a shared table is defined can cause an error. However, the LOCK statement need not be executed if the value of the column for which an index is defined is not changed. For details about updating a shared table, see the HiRDB Version 9 Installation and Design Guide. For objects of locking for the execution of the LOCK statement for a shared table, see the notes in LOCK statement (Lock control on tables).
  14. If the table to be updated meets all of the following conditions and if columns in the table are to be updated by DEFAULT, the LOCK statement in the locking mode should be executed on the shared table before updating; an attempt to update the table without executing the LOCK statement can cause an error:
    • The table to be updated is a shared table.
    • The column to be updated is of the timestamp data type.
    • When the table was defined, CURRENT_TIMESTAMP USING BES was specified as the default.
Format 4 rules
  1. If a table with a WITHOUT ROLLBACK specification for which an index is defined is specified in table-identifier, updating cannot be executed if the update value for the index constituent column is other than the pre-update value. For details, see the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table) in Chapter 3.
  2. If the table to be updated is a shared table and if an index is defined for one of the columns, the LOCK statement should be executed in the lock mode on the shared table before updating. An attempt to update the shared table by row (ROW specification) without executing the LOCK statement can cause an error. However, the LOCK statement need not be executed if the value of the column for which an index is defined is not changed. For details about updating a shared table, see the HiRDB Version 9 Installation and Design Guide. For objects of locking for the execution of the LOCK statement on a shared table, see the notes in LOCK statement (Lock control on tables).
  3. Because the UPDATE statement by row updates the values of all columns, the UPDATE statement by row cannot be executed on a falsification-prevented table that contains even a single non-updatable column.
  4. The UPDATE statement cannot be executed by row for tables that contain columns of character data that do not use the default character set.
Rules common to Formats 3 and 4
  1. One or more variable arrays should be specified in a clause other than the FOR clause. A failure to specify a variable array can cause an error.
  2. Specifying an embedded variable not in the array format in a clause other than the FOR clause can cause an error.
  3. The number of elements in the embedded variable array or the indicator variable array should be in the 1 to 4,096 range. Specifying an out-of-range value can cause an error. Such a number should be greater than or equal to the maximum number of elements specified in FOR :embedded-variable.
  4. The elements that are updated in one updating operation in an embedded variable array or indicator variable array are elements that have the same element numbers.
  5. Evaluation is performed sequentially from the first element of an array.
  6. If more than one updating operation is performed, the object of updating is an update object after the previous updating using array elements was performed.
  7. The total number of updated rows, including any rows updated in duplicate, is assigned to the SQLERRD[2] area of the SQL communications area.
  8. Because it includes embedded variable arrays and indicator variable arrays, UPDATE statement Format 3 cannot be preprocessed by the PREPARE statement. For details about dynamic execution, see EXECUTE statement Format 2 (Execute an SQL statement using an array).
  9. An UPDATE statement using an array cannot be used in a procedure.
  10. If an event that requires warning occurs in any of the rows to be updated, warning information is assigned to the SQLWARN flag of the SQL communications area.
  11. If an error occurs in any of the rows to be updated, the transaction is rolled back.
  12. The UPDATE statement cannot be specified for a falsification-prevented table. If a falsification-prevented table is defined, the rules on Formats 3 and 4 must be observed.
  13. If RDAREA-name-specification is specified, you cannot use an index in which the number of partitions is different from the number of partitions of the table. When defining an index for queries that specify RDAREA-name-specification, define an index that has the same number of partitions as the number of partitions of the table.

Rules on referential constraints

  1. For the rules on updating the primary key of a referenced table and the foreign key of a referencing table, or updating a referenced table or referencing table row by row, see the explanation of referencing actions in CREATE TABLE (Define table) in Chapter 3.
  2. For the updating of values of a foreign key constituent column in a referenced table for which constraint operations are defined in RESTRICT, the referencing table is searched to determine whether the updating value is included in the value of a foreign key constituent column in the referencing table. The data guarantee level during the search through the referencing table assumes the share mode. For this reason, if during the deletion of rows in the referenced table for which constraint operations are defined in RESTRICT, operations are performed on the referencing table by another transaction, the row deletion action goes into a wait state until the transaction is settled.
  3. If any combination of the following conditions occurs, data incompatibility can occur between the referenced table and the referencing table subject to referential constraints; such incompatibility can also occur regardless of whether the constraint operation is RESTRICT or CASCADE:
    • The transaction that deletes rows in the referencing table is different from the transaction that updates or deletes rows in the referenced table.
    • The above two transactions are executed simultaneously.
    • The value of the primary key constituent column deleted from the referencing table is the same as the value of the foreign key constituent column that is either updated in or deleted from the referenced table.
    • Either the transaction that deletes rows from the referencing table is committed or the transaction that updates or deletes rows in the referenced table is rolled back.
  4. If a combination of the following conditions occurs, a deadlock can occur between the transaction that manipulates the referenced table and the transaction that manipulates the referencing table. A deadlock can also occur if the constraint operation is either RESTRICT or CASCADE.
    • The transaction that updates the rows in the referencing table is different from the transaction that updates the rows in the referenced table.
    • The above two transactions are executed simultaneously.
    • The value of the foreign key constituent column updated in the referencing table is the same as the value of the primary key constituent column that is deleted from the referenced table.

Examples

  1. The values of the product code (PCODE) and quantity in stock (SQTY), which are stored in an array variable written in C, are listed in the tables below. The values of the quantity in stock (SQTY) are to be updated using the UPDATE statement Format 1.

    Table 4-7 Product code and inventory level stored in the table (before updating)

    Product codePre-update inventory level
    '101M'40
    '101L'70
    '201M'15
    '202M'28
    '302S'7

    Table 4-8 Product code and inventory level (assigned to the embedded variable array) in the row to be updated

    Product codeUpdated inventory level
    '101M'35
    '101L'62
    '201M'13
    '202M'10
    '302S'6

    Table 4-9 Product code and inventory level stored in the table (after update)

    Product codeUpdated inventory level
    '101M'35
    '101L'62
    '201M'13
    '202M'10
    '302S'6

    EXEC SQL BEGIN DECLARE SECTION;
         short   XUPDATE_NUM;
         char    XPCODE[5][5];
         short   IPCODE[5];
         long    XSQTY[5];
         short   ISQTY[5];
    EXEC SQL END DECLARE SECTION;
       . . . Assign values to elements in the array variables . . .
           Assign{'101M','101L','201M','202M','302S'} to XPCODE
           Assign{35,62,13,10,6} to XSQTY
    XUPDATE_NUM = 5;
    EXEC SQL FOR :XUPDATE_NUM
    UPDATE STOCK SET SQTY = :XSQTY:ISQTY
    WHERE PCODE = :XPCODE:IPCODE;

  2. Use UPDATE statement Format 2 to update an entire row in the inventory table (STOCK) by value of the product code (PCODE) assigned to an array variable in the C language, with the contents on the embedded variable array (XROW) in batch.

    XUPDATE_NUM = 5;
    EXEC SQL FOR :XUPDATE_NUM
    UPDATE STOCK SET ROW = :XROW
    WHERE PCODE = :XPCODE:IPCODE;