DELETE statement Format 1 (Delete rows)

Function

The DELETE instruction deletes from a table the rows that satisfy a specified search condition or the row indicated by a cursor.

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

DELETE FROM [[RD-node-name.]authorization-identifier.]table-identifier
                [ [AS] correlation-name]
           [SQL-optimization-specification-for-used-index]
           [WHERE {search-condition|CURRENT OF {cursor-name | extended-cursor-name }}]
           [WITH ROLLBACK]

Operands

RD-node name
Specify the RD node name of the RD node to be accessed.
authorization-identifier
Specifies the authorization identifier of the user who owns the table.
MASTER cannot be specified as an authorization identifier. For the case in which the authorization identifier is omitted, see 1.1.8 Qualifying a name.
table-identifier
Specifies a table containing the row to be deleted.
The following rules apply to table identifiers:
  1. Row insertion, updating, or deletion cannot be performed on a read-only view table.
  2. For details of read-only view tables, see the Common Rules in CREATE VIEW (Define view) for definition SQL statements.
  3. If the deletion of a row from a view table is specified, HiRDB deletes the row from the base table which is subject to the view table operation.
  4. The scope of table-name is the entire DELETE statement.

Specify this operand when using a correlation name for the table to be deleted.

The scope of the correlation name is the entire DELETE statement. The table identifier to be deleted does not have a scope.

For details about the SQL optimization specification for a used index, see 2.24 SQL optimization specification.

WHERE
When the WHERE clause is omitted, all rows in the specified table are deleted and the W warning flag is set in SQLWARN4 in the SQL Communications Area.
search-condition
Specifies criteria for selecting the rows to be deleted.
Embedded variables, ? parameters, SQL variables, and SQL parameters can be specified in a search condition. Only ? parameters can be specified in a search condition in a DELETE statement that is preprocessed by the PREPARE statement.
SQL variables or SQL parameters are used in an SQL procedure. For details about specification values in a Java procedure, see JDBC drivers or SQLJ in the HiRDB Version 8 UAP Development Guide. For details on search conditions, see 2.7 Search conditions.

Specifies the name of the cursor that indicates the row to be deleted.

The cursor name specified in cursor-name is a cursor declared in a cursor declaration.

The cursor specified in cursor-name must be positioned at a row opened by the OPEN statement and closed by the FETCH statement. Any OPEN, FETCH, CLOSE, or DELETE statement that is directed at the cursor should be executed in the same transaction (except when a holdable cursor is used).

After the DELETE statement has executed, the cursor specified in cursor-name becomes a cursor that does not point to any row. To update or delete the row following the deleted row, the FETCH statement must be executed on the cursor in order to advance the cursor.

extended-cursor-name
This operand specifies the extended cursor name that identifies the cursor that points to the row to be deleted.
The extended cursor name that identifies the cursor allocated by the ALLOCATE CURSOR statement should be specified. However, a result set cursor cannot be specified.
The cursor identified in extended-cursor-name should be open and positioned on the row to be deleted.
The cursor identified in extended-cursor-name does not have any row that it can point to, after the execution of the DELETE statement. If any row after the deleted row is to be updated or deleted, the FETCH statement should be executed on the cursor to move it.
For extended cursor names, see 2.27 Extended cursor name.

Common rules

  1. Normal execution of the DELETE statement sets the number of deleted rows in SQLERRD[2] in the SQL Communications Area.
  2. If there are no rows to be deleted, the system returns the following return codes:
    • Return code 100 to SQLCODE in the SQL Communications Area
    • Return code 100 to the SQLCODE variable
    • Return code '02000' to the SQLSTATE variable
  3. If the user LOB RDAREA that stores a LOB column or LOB attribute is in the frozen update status, the LOB column or LOB attribute cannot be deleted (an attempt to delete it causes an already frozen error).
  4. If the table is a falsification prevented table and the rows that satisfy specified search conditions include a row that is subject to a deletion prohibition duration, HiRDB deletes none of the rows satisfying the search conditions, and generates an error.
  5. If the table is a falsification prevented table and the row pointed to by a specified cursor is subject to a deletion prohibition duration, HiRDB generates an error without deleting the row.
  6. If the DELETE statement is executed on a table with a WITHOUT ROLLBACK specification, the timing at which the row-locking is released can vary according to whether an index is defined. For details, see the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table).
  7. Before deleting a row in a shared table, the LOCK statement for the table should be executed in the lock mode. An attempt to delete a row in a shared table without executing the LOCK statement can cause an error that prevents the row from being deleted. For details about how to update shared tables, see the HiRDB Version 8 Installation and Design Guide. For objects of locking in the execution of the LOCK statement, see the notes in LOCK statement (Lock control on tables).

Rules on referential constraints

  1. For rules on deleting rows in a referenced table or referencing table, see the reference operation in CREATE TABLE (Define table).
  2. 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.
  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. For rules on referential constraints, see the HiRDB Version 8 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.

Notes

  1. Similar to an embedded variable, a cursor name is effective within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in different modules.
  2. If the table is a falsification prevented table and a deletion prohibition duration is specified, an overflow occurs if the sum of the date of insertion of the row to be deleted and the deletion prohibition duration exceeds December 31, 9999.

Examples

  1. Delete from stock table STOCK those rows whose product code (PCODE) column is '302S':

    DELETE FROM STOCK
          WHERE PCODE = '302S'

  2. Delete from stock table STOCK those rows whose product code (PCODE) column is read into embedded variable :XPCODE:

    DELETE FROM STOCK
          WHERE PCODE = :XPCODE

  3. Delete the row specified by cursor CR1 from stock table STOCK:

    DELETE FROM STOCK
          WHERE CURRENT OF CR1