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 have no effect, regardless of the current DML
ATOMICITY. Nothing has 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:
When deleting rows from a TurboIMAGE data set.
If a SET CONSTRAINTS DEFERRED statement is in
effect.
If a rule exists on the table and rules are enabled for the
DBEnvironment. Consider issuing a DISABLE RULES
statement to temporarily disable rules for the DBEnvironment,
issuing the DELETE WITH AUTOCOMMIT statement, and then
issuing an ENABLE RULES statement to turn rule checking
back on.
In the DELETE WHERE CURRENT statement.
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.