UPDATE statement Format 2 (Update data)
Function
Updates row by row the values of the table rows that satisfy the specified search condition or are indicated by a cursor.
Privileges
A user who has the UPDATE privilege for a table can update the row 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 2: Updating rows in a table with the FIX specification on a row-by-row basis
UPDATE [[RD-node-name.]authorization-identifier.]table-identifier [[AS] correlation-name]
[used-index-SQL-optimization-specification]
SET ROW= row-update-value
[SQL-optimization-specification-for-used-index]
[WHERE {search-condition|CURRENT OF {cursor-name|extended-cursor-name}}]
[WITH ROLLBACK]
Operands
All operands other than ROW and the operand rules for them are the same as for Format 1 of the UPDATE statement. The following rules apply to specifying ROW:
- Row-by-row updating can be specified only on a base table with the FIX attribute. The operand ROW refers to an entire row. When ROW is specified, HiRDB treats the entire row as one set of data. The data type of the data used for updating should be the ROW type, regardless of the data types of the individual columns. (Variables corresponding to CHAR(n) (where n is the row length) or structures of the same length can be specified as ROW-type data; if a structure is specified, the structure should not contain any boundary alignment blanks.) The data length should be equal to the row length (the sum of the data lengths of the columns).
- 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 version UAP, the HiRDB server should also use the Windows version of the same endian.
Specifies that column values are to be updated.
- ROW
- Specifies that data is to be updated on a row-by-row basis.
- row-update-value
- Any of the following items can be specified as the row update values corresponding to ROW:
- Embedded variables (and indicator variables)
- ? parameters
- SQL variables or SQL parameters
Common rules
- 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 in which at least one column is not updatable.
- 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.
- 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 8 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).
Rules on referential constraints
- For the rules on row-by-row updating of rows in a referenced table or referencing table, see the explanation of referencing actions in CREATE TABLE (Define table) in Chapter 3.
- For the updating of rows 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.
- 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. For rules on referential constraints, see the HiRDB Version 8 Installation and Design Guide.
- 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.
- 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. For details about a deadlock between referenced and referencing tables, see the HiRDB Version 8 Installation and Design Guide.
- The transaction that deletes the rows in the referencing table is different from the transaction that updates or deletes 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.
Notes
- If the data type of the table columns to be updated is DECIMAL, or a national character string, the system checks the contents of the applicable row update values.
- A cursor name, similar to an embedded variable name, is effective only within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in multiple modules.
- When performing retrieval or updating by row (ROW specification), the date data type portion of the embedded variable, the SQL variable, or the SQL parameter with respect to ROW is 4 bytes long, and is specified in an X'YYYYMMDD' format.
When receiving date data in a predefined character string representation using a by-row (ROW specification) interface, when defining a column, define it as CHAR(10) rather than a date data type.
For date operations, use the DATE scalar function, to be specified after the data is converted into the date data type.
- When performing retrieval or updating by row (ROW specification), the time data type portion of the embedded variable, the SQL variable, or the SQL parameter with respect to ROW is 3 bytes long, and is specified in an X'hhmmss' format.
When receiving time data in a predefined character string representation using a by-row (ROW specification) interface, when defining a column, define it as CHAR(8) rather than a time data type. For time operations, use the TIME scalar function, to be specified after the data is converted into the time data type.
- For performing a retrieval or updating by row (ROW specification), the time stamp data type portion of the embedded variable, SQL variable, or SQL parameter with respect to ROW is (7 + p/2) byte long, and it should be in the X'YYYYMMDDhhmmss[nn...n]' format.
When receiving time stamp data in a predefined character string representation using a by-row (ROW specification) interface, when defining a column, define it as CHAR with a length of 19, 22, 24, or 26 bytes rather than as a time stamp data type column.
Example
In a stock table named STOCK, update in a single operation the data in the row specified by cursor CR1 with the contents of embedded variable XROW:
UPDATE STOCK
SET ROW = :XROW
WHERE CURRENT OF CR1