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.
- [authorization-identifier.]table-identifier
- 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 about read-only view tables, see the common rules under CREATE [PUBLIC] VIEW (Define view, define public view) in Chapter 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.
- The scope of table-name is the entire DELETE statement.
- [IN (RDAREA-name-specification)]
- IN
- Specifies the RDAREA to access.
- RDAREA-name-specification ::= value-specification
- Of the RDAREAs that contain the table specified in the table identifier, specify the name of the RDAREA to access as a value-specification of type VARCHAR, CHAR, MVARCHAR, or MCHAR. If multiple RDAREA names are specified, separate them with a comma (,). RDAREA names must be unique; an error occurs if duplicate RDAREA names are specified. For details about what characters are allowed in RDAREA names in value-specification, see 1.1.7 Specification of names. Note also that leading and trailing whitespace is ignored in RDAREA names specified in value-specification. If the RDAREA name is enclosed in double quotation marks ("), only whitespace outside the double quotation marks is ignored.
- If cursor-name or extended-cursor-name is specified, specify the same set of RDAREAs (in any order) as the RDAREAs specified in the cursor declared in the cursor declaration. An error occurs if they are not specified.
- If specifying an RDAREA that uses the inner replica facility, specify the original RDAREA name. To target the replica RDAREA, use the change current database command (pddbchg command), or the PDDBACCS operand in the client environment definition, to switch the RDAREA to access to the replica RDAREA.
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 9 UAP Development Guide. For details about 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.
- If extended-cursor-name is specified, an extended cursor must be specified for queries that specify the FOR UPDATE clause. For details about the FOR UPDATE clause, see the FOR UPDATE clause under Operands in Dynamic SELECT statement Format 1 (Retrieve dynamically) in this chapter.
- 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.