DELETE

If all the rows of a table are deleted, the table is empty but continues to exist until you issue a DROP TABLE statement.

Use the TRUNCATE TABLE statement to delete all rows from a table instead of the DELETE statement. The TRUNCATE TABLE statement is faster, and generates fewer log records.

If ALLBASE/SQL detects an error during a DELETE statement, the action taken will vary, depending on the setting of the SET DML ATOMICITY, and the SET CONSTRAINTS statements. Refer to the description of both of these statements in this chapter for more details.

Using DELETE with views requires that the views be based on updatable queries. See "Updatability of Queries" in the "SQL Queries" chapter.

The target table of the DELETE statement is specified with TableName or is the base table underlying the view definition of ViewName. It must be an updatable table, and it must not appear in the FROM clause of any subquery specified in the SearchCondition parameter or any subquery of ViewName.

The search condition is effectively executed for each row of the table or view before any row is deleted. If the search condition contains a subquery, each subquery in the search condition is effectively executed for each row of the table or view and the results used in the application of the search condition to the given row. If any executed subquery contains an outer reference to a column of the table or view, the reference is to the value of that column in the given row.

A deletion from a table with a primary key (a referenced unique constraint) fails if any primary key row affected by the DELETE statement is currently referred to by some referencing foreign key row. In order to delete such referenced rows, you must first change the referencing foreign key rows to refer to other primary key rows, to contain a NULL value in one of the foreign key columns, or to delete these referencing rows. Alternatively, you can defer error checking (with the SET CONSTRAINT statement) and fix the error later.

The DELETE syntax is unchanged for use with LONG columns. It is limited in that a LONG column cannot be used in the WHERE clause. When LONG data is deleted, the space it occupied in the DBEnvironment is released when your transaction ends. But the physical operating system data file created when you selected the long field earlier still exists and you are responsible for removing it if you desire.

A check constraint search condition defined on a table never prevents a row from being deleted, whether or not constraint checking is deferred.

A rule defined with a StatementType of DELETE will affect DELETE statements performed on the rule's target table. When the DELETE is performed, each rule defined on that operation for the table is considered. If the rule has no condition, it will fire for all rows affected by the statement and invoke its associated procedure with the specified parameters on each row. If the rule has a condition, it will evaluate the condition on each row. The rule will fire on rows for which the condition evaluates to TRUE and invoke the associated procedure with the specified parameters for each row. Invoking the procedure could cause other rules, and thus other procedures, to be invoked if statements within the procedure trigger other rules.

If a DISABLE RULES statement is in effect, the DELETE statement will not fire any otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable rules will fire again, but only for subsequent DELETE statements, not for those processed when rule firing was disabled.

In a rule defined with a StatementType of DELETE, any column reference in the Condition or any ParameterValue will refer to the value of the column as it exists in the database before it is removed by the DELETE statement, regardless of the use of OldCorrelationName, TableName, or NewCorrelationName in the rule definition.

The set of rows to be affected by the DELETE statement is determined before any rule fires, and this set remains fixed until the completion of the rule. If the rule adds to, deletes from, or modifies this set, such changes are ignored.

When a rule is fired by this statement, the rule's procedure is invoked after the changes have been made to the database for that row and all previous rows. The rule's procedure, and any chained rules, will thus see the state of the database with the current partial execution of the statement.

If an error occurs during processing of any rule considered during execution of this statement (including execution of any procedure invoked due to a rule firing), the statement and any procedures invoked by any rules will have no effect, regardless of the current DML ATOMICITY. Nothing will have been altered in the DBEnvironment as a result of this statement or the rules it fired. Error messages are returned in the normal way.

When the WITH AUTOCOMMIT clause is not used, rows that qualify according to the SearchCondition are deleted internally in batches by ALLBASE/SQL.

When the WITH AUTOCOMMIT clause is used, a COMMIT WORK statement is executed automatically at the beginning of the DELETE statement and also after each batch of rows is deleted. This can reduce both log-space and shared-memory requirements for the DELETE statement. You cannot control the number of rows in each batch.

The WITH AUTOCOMMIT clause cannot be used in these cases:

If an active transaction exists when the DELETE WITH AUTOCOMMIT is issued, then the existing transaction is committed.

When WITH AUTOCOMMIT is used, any previously issued SET DML ATOMICITY statements are ignored. For the duration of that DELETE command, row-level atomicity is used.

If the DELETE WITH AUTOCOMMIT statement fails, it may be true that some (but not all) rows that qualify have been deleted.

The DELETE WITH AUTOCOMMIT statement can be used in procedures, but a rule may not execute that procedure.