DELETE statement Format 2 (Delete row using an array)
Function
Deletes rows that satisfy specified search conditions from a table. Multiple deletion actions can be executed in batch by specifying an embedded variable of an array format.
Privileges
A user who has the DELETE privilege for a table can delete rows from 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: Deleting several times using an embedded variable array
FOR : embedded-variable
DELETE FROM [authorization-identifier.]table-identifier
[IN (RDAREA-name-specification)][[AS] correlation-name]
[used-index-SQL-optimization-specification]
WHERE search-condition
[WITH ROLLBACK] |
Operands
See Format 1 for details about the operands and operand rules other than search conditions and FOR, IN (RDAREA-name-specification).
Specifies the embedded variable in which the number of times deletion 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 be used as a search condition using a value other than the NULL value. Values to be used as search conditions should be assigned to the elements of the variable array. If a value to be used as a search condition 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.
- [IN (RDAREA-name-specification)]
If RDAREA-name-specification is specified as an embedded variable, it must be specified as an embedded variable array. Specifying an embedded variable that is not an array results in an error. See Format 1 for details about other operand rules.
- search-condition
- Specifies the criteria by which the rows to be deleted are selected. An embedded variable not in the array format cannot be specified in search-condition.
Common rules
- One or more variable arrays should be specified in a clause other than the FOR clause. An error may occur if such an array is not specified.
- Specifying an embedded variable not in the array format in a clause other than the FOR clause can cause an error.
- The data type of embedded-variable-array should be the data type of the corresponding column or a convertible data type.
- The number of elements in the embedded variable array or the indicator variable array should be in the range of 1 to 4,096. Specifying an out-of-range value can cause an error. The number of elements should be greater than the maximum value specified in FOR:embedded-variable.
- The elements that are evaluated in one deletion operation in a given embedded variable array and indicator variable array are the elements having the same element number.
- Because it contains embedded variable arrays and indicator variable arrays, the DELETE statement Format 2 cannot be preprocessed by a PREPARE statement. For details about how to execute DELETE statement Format 2 dynamically, see EXECUTE statement Format 2 (Execute an SQL statement using an array).
- DELETE using an array cannot be used in a procedure.
- DELETE using an array cannot accept the BLOB type, the BINARY type with a maximum length of 32,001 bytes or greater, or the abstract data type.
- Upon normal completion of the DELETE statement, the number of rows deleted is assigned to the SQLERRD[2] area of the SQL communications area.
- If the row to be deleted is not found, the following return codes are assigned:
- A return code of 100 to the SQLCODE area of the SQL communications area
- A return code of 100 to the SQLCODE variable
- A return code of 02000 to the SQLSTATE variable
- If the given table is a falsification-prevented table, and if the rows satisfying specified search conditions include a row in the deletion prohibited duration, an error occurs, and the command terminates without deleting any of the rows satisfying the search conditions.
- If an error occurs in any of the rows to be deleted, the DELETE statement is rolled back.
- If the DELETE statement is executed on a table with a WITHOUT ROLLBACK specification, the timing at which the row-locking is released varies according to whether or not an index is defined. See the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table).
- Before deleting rows in a shared table, the LOCK statement in the lock mode should be executed on the table. An attempt to delete rows in a shared table without executing the LOCK statement can cause an error and a failure to delete the rows. For details about how to perform updates on a shared table, see the HiRDB Version 9 Installation and Design Guide. For objects of locking in the execution of the LOCK statement on a shared table, see the notes in LOCK statement (Lock control on tables).
- 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
- For rules on deleting rows in a referenced table or referencing table, see the reference operation in CREATE TABLE.
- For the deletion of rows in a referenced table for which constraint operations are defined in RESTRICT, the referencing table is referenced to determine whether the value of the primary key constituent column in the rows to be deleted 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 9 Installation and Design Guide.
- The transaction involving the deletion of 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 for the row in the referencing table to be deleted is equal to the value of the foreign key constituent column of the row in the referenced table to be updated or deleted.
- The transaction that deletes referencing table rows is committed, and the transaction that updates or deletes referenced table rows is rolled back.
Examples
- Execute, in batch, several deletion operations on rows of the inventory table (STOCK) by value of the product code (PCODE) that is assigned to an array variable in the C language:
XDELETE_NUM = 5;
EXEC SQL FOR :XDELETE_NUM
DELETE FROM STOCK
WHERE PCODE = :XPCODE:IPCODE;