DELETE (Positioned)

The DELETE statement removes any number of records from a table or view. The table or view can be within the current location or any other location with which the XDB Server can establish a connection, provided that the user has privileges in that other location. Deleting a row from a view actually deletes the row from the base table on which the view was created (if it is an updatable view).
Restriction: This topic applies to Windows environments only.

Invocation

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

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.

Syntax

DELETE FROM {table-name | view-name} 
    [correlation-name] 
    WHERE CURRENT OF cursor-name
    [FOR ROW [host-variable | integer-constant] OF ROWSET]

Parameters:

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.

Description

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.

Index Considerations

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.

FOREIGN KEY Considerations

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.

DELETE vs. TRUNCATE TABLE

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.

WHERE CURRENT OF

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.

QUERYNO integer

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.

Examples:

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;