DELETE is an executable command that can be issued interactively or embedded in a host language. DELETE can be dynamically prepared. The Positioned DELETE command (explained below) can be used only with embedded SQL.
Authorization requirements depend on whether the object identified in the statement is a table or a view. To execute the DELETE command on a table, users must possess either DBADM authority for the database containing the table, DELETE privilege on the table, overall SYSADM authority for the location, or be the owner of the table.
To execute the DELETE command on a view, users must possess the DELETE privilege on the view or have overall SYSADM authority. For more information on view privileges, see CREATE VIEW. If a subselect is specified within the view or table, the user must have the necessary privileges to execute the subselect.
To execute a DELETE at another location, the set of available privileges consists only of those privileges recorded in the system catalog at the location where the DELETE statement is to be executed.
DELETE FROM {table-name | view-name}
[correlation-name]
WHERE CURRENT OF cursor-name
[FOR ROW [host-variable | integer-constant] OF ROWSET]
| table-name or view-name | Names an object (table or view) from which records are to be deleted. If a view name is identified, the view must be an updatable view since the underlying table will be affected. The named table or view may exist at a location other than the current location, as long as the correct location is either implicitly specified (SET LOCATION TO or CONNECT command precedes the DELETE command) or explicitly specified in the object name (location-name.AuthID.object-name). |
| correlation-name | Identifies an optional correlation name that then qualifies table or view column references within the search-condition. |
| search-condition | Determines which records to delete. Can be any valid query search condition, with each column name (other than in a subquery) identifying a column in table-name or view-name. The search condition must not include a subquery that also operates on either table-name or view-name. |
| cursor-name | Identifies a previously defined, opened, and fetched cursor, which points to the row to be deleted. |
Both Searched DELETE and Positioned DELETE are supported -- with the first type of command deleting one or more rows (with the option of including a WHERE clause), and the second type deleting only one row at a time, using the row pointer position within a cursor result table. When the DELETE command is embedded in a host language such as COBOL, C, or PL/I, it can contain host variables.
The Positioned DELETE can only be used in embedded SQL, since it involves the use of a previously defined cursor. When a positioned DELETE is executed, the row that the cursor currently points to is deleted.
When a record is deleted, the XDB Server simply marks the record as deleted. The data are still present in the table. If the Backward Log is set to ON, the ROLLBACK command can remove these delete markers and return the records to their former state. See the SELECT command for information on how to view records marked for deletion.
If an index exists on a table, deleting records automatically updates the index. This could lead to a performance penalty if many records are deleted because each record deleted will cause the index to be updated. When deleting a large number of records from a table, it is sometimes more efficient to drop the index, delete the records, then re-create the index.
When deleting records from a table involved in a FOREIGN KEY constraint, the rule defined for the FOREIGN KEY will be followed. See the ALTER TABLE and CREATE TABLE commands for more information on deleting records containing FOREIGN KEYs.
When deleting all records from a table, the TRUNCATE TABLE command can save time and system overhead. TRUNCATE TABLE deletes all records in a table without logging the deleted records or maintaining the table indexes. The system will not be able to rollback the deleted records. This command is not allowed on a table containing a primary key, since it may be referenced by another table.
This keyword clause identifies the cursor to be used in a positioned DELETE statement. The cursor-name specified must identify an already declared cursor. When a DELETE statement is executed, the cursor must already be positioned on the row to be deleted. After deleting the row, a FETCH statement should be executed to retrieve the next result table row.
Specifies the number to be used for this SQL statement in EXPLAIN output and trace records. The number is used for the QUERYNO columns of the plan tables for the rows that contain information about this SQL statement.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful for simplifying the use of optimization hints for access path selection, if hints are used.
A sample DELETE statement appears below:
DELETE FROM partsupp
WHERE sno = "S1"
This command deletes all the records where the supplier is S1.
The more complex example shown below deletes all suppliers who supply part P4:
DELETE FROM supplier
WHERE sno IN
SELECT sno
FROM partsupp
WHERE pno = "P4"
If the optional WHERE clause is not included, all records in the table are deleted. After the DELETE command is issued, the table will not contain any active records, but the table still exists and still appears in the directory of active tables. You cannot recover deleted records if DELETE is issued without a WHERE condition. You can reenter new data at any time using either the INSERT command or the SQLWizard data entry facility. For example, the following command removes all of the records from the SUPPLIER table:
DELETE FROM supplier
When the DELETE command is embedded in a host language, it must appear within the "EXEC SQL ... END-EXEC" or "EXEC SQL ...;" delimiters, as required by the XDB Server host language preprocessor. The following COBOL language example shows a positioned DELETE command. It is assumed that the cursor EMPTBL has been declared, opened and fetched to the row desired:
EXEC SQL
DELETE FROM employee
WHERE CURRENT OF emptbl;