DELETE is an executable command that can be issued interactively or embedded in a host language. DELETE can be dynamically prepared.
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 ownership 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.
The SELECT privilege is required in addition to the DELETE privilege when the option for the SQL standard is set as follows:
DELETE FROM {table-name | view-name} [correlation-name]
[WHERE search-condition]
[WITH {CS | RR}][QUERYNO integer]
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 Searched DELETE uses the standard SQL syntax for a DELETE statement, which relies on a WHERE condition to determine the records to be deleted. The WHERE condition is defined in the same way as it would be for a SELECT command. To determine which records a searched DELETE command will delete, first SELECT from the table using the same WHERE condition. See WHERE Clause for more information on searching for records.
When using subselects as search conditions, the subselect cannot reference a location other than the one referenced elsewhere in the command. That is, a referenced table and all subselects in a single SQL statement must be from the same location.
Invalid:
DELETE FROM tutorial.tutorial.employee WHERE e_no IN SELECT e_no FROM debloc.debbie.employee WHERE st = "MD";
Valid:
DELETE FROM tutorial.tutorial.employee WHERE e_no IN SELECT e_no FROM tutorial.debbie.employee WHERE st = "MD";
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 identifies rows to be deleted in a searched DELETE. If WHERE is omitted, all rows of the table or view named are deleted. The search condition following the WHERE keyword is applied to each row of the named table or view, deleting all rows for which the search condition is true. A subquery with no correlated references will be executed just once. A subquery with a correlated reference will be executed once for each row identified in the WHERE clause.
Specifies an isolation level with which the statement is executed in Searched DELETE statements. You can override the isolation level of the statement using the new WITH {RR | CS} clause. Because WITH UR only applies to read only operations, you cannot use it on a DELETE statement. The WITH clause overrides the default isolation level only for the statement in which it appears.
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.
If SQL standard rules are in effect and the search-condition in a searched DELETE contains a reference to a column of the table or view, the privilege set must include at least one of the following:
SQL standard rules are in effect as follows:
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 searched DELETE command containing a host variable in the WHERE clause:
EXEC SQL
DELETE FROM employee
WHERE lname = :ENAME
END-EXEC