DELETE
Function
The DELETE statement removes a particular row or set of rows from the target table. There are two forms of this statement - positioned DELETE and searched DELETE.
Syntax
DELETE FROM table_specification [correlation_identifier [,]] [WHERE [SEARCH CONDITION | CURRENT OF cursor_identifier]]
where
table_specification | The table to be amended. The table must be defined at compilation time. If the table specification is a view reference, then the view must be updatable. See
Table Specification. |
correlation_identifier | |
search_condition | The specification of a resultant table which is to be deleted from the target base table. See
Search Condition. |
cursor identifier | A valid identifier of no more than 18 characters and which has not previously been used as a cursor identifier within the same compilation unit. |
Description
A DELETE statement removes from the target table the row or rows identified in the WHERE clause.
Rows in Level 1 or level 2 tables can not be deleted directly using a DELETE statement. They can only be removed by deleting the associated level 0 row in the master table. The referencing level 1 and level 2 rows are automatically deleted with the level 0 row. This is analogous to a DELETE CASCADE in pure referential integrity terminology.
A DELETE statement with a WHERE CURRENT OF cursor identifier as its means of identifying the row to be deleted is called a positioned DELETE statement .
If the DELETE statement is positioned, then only the row to which the cursor is currently pointing is deleted. Hence, the cursor must be OPEN and pointing to a row otherwise a runtime error will occur. In addition, the cursor must be in itself updatable. See DECLARE CURSOR for further details. Once the row has been deleted, the cursor is not advanced, it simply no longer points to a row.
A DELETE statement with a WHERE search condition is called a searched DELETE statement. If the DELETE is searched, a resultant table is established at execution time in a similar manor to a query specification. Each row in the target table which has a corresponding row in the resultant table is, then deleted.
A DELETE statement without any WHERE clause is really a special case of the searched DELETE alternative as a resultant table is established which contains all the rows of the target table. In such a case, all rows of the table are deleted.
Limitations
If the specified table is in fact a view, then that view must be updatable.
ANSI Specifics
A positioned DELETE statement must appear in the same compilation unit as the associated DECLARE and OPEN and must appear physically after the DECLARE.
The use of correlation identifiers in this context is not supported in ANSI-compatibility mode.
CONNX Specifics
A positioned DELETE statement can be in a different compilation unit to that of the associated DECLARE as long as a FOR UPDATE clause is specified. If the DELETE is in the same compilation unit as the associated DECLARE CURSOR statement, then there is no restriction as to the relative positions of the two statements.
The possibility to use a correlation identifier is CONNX extension.
If your ADABAS file uses superdescriptors, Superdescriptor Handling contains information to minimize search time.
Examples
The following example deletes all cruises that depart from VIRGIN ISLANDS:
DELETE FROM cruise
WHERE start_harbor = 'VIRGIN ISLANDS';
The following example deletes ALL information contained within table `cruise':
DELETE FROM cruise;
The following example deletes the row in table cruise to which a cursor named 'cursor1' is currently pointing:
DELETE FROM cruise
WHERE CURRENT OF cursor1;