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.
- [[RD-node-name.]authorization-identifier.]table-identifier
- 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:
- Row insertion, updating, or deletion cannot be performed on a read-only view table.
- For details of read-only view tables, see the Common Rules in CREATE VIEW (Define view) for definition SQL statements.
- 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.
- 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.
- SQL-optimization-specification-for-used-index
For details about the SQL optimization specification for a used index, see 2.24 SQL optimization specification.
- WHERE {search-condition|CURRENT OF {cursor-name | extended-cursor-name} }
- 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.
- WITH ROLLBACK
Specifies that if the table to be deleted is being used by another user, the transaction issued by that user is to be cancelled and invalidated.
If the WITH ROLLBACK option is omitted and the table to be deleted is being used by another user, the current user must wait until the transaction issued by the other user is completed.