HP 3000 Manuals HP 3000 Manuals
DELETE
The DELETE statement deletes a row or rows from a table.
Scope
ISQL or Application Programs
SQL Syntax
[REV BEG]
DELETE [WITH AUTOCOMMIT] FROM {[Owner.]TableName} [WHERE SearchCondition]
{[Owner.]ViewName }
[REV END]
Parameters
[REV BEG]
WITH AUTOCOMMIT executes a COMMIT WORK automatically at the
beginning of the DELETE statement and also after
each batch of rows is deleted.[REV END]
[Owner.]TableName designates a table from which any rows satisfying
the search condition are to be deleted.
[Owner.]ViewName designates a view based on a single table.
ALLBASE/SQL finds which rows of the view satisfy
the search condition; the corresponding rows of
the view's base table are deleted. Refer to the
CREATE VIEW statement for restrictions governing
modifications via a view.
WHERE SearchCondition specifies which rows are to be deleted. If no
rows satisfy the search condition, the table is
not changed. If the WHERE clause is omitted, all
rows are deleted.
Description
* 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.
[REV BEG]
* 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.[REV END]
Authorization
If you specify the name of a table, you must have DELETE or OWNER
authority for that table or you must have DBA authority.
If you specify the name of a view, you must have DELETE or OWNER
authority for that view or you must have DBA authority. Also, the owner
of the view must have DELETE or OWNER authority with respect to the
view's base tables, or the owner must have DBA authority.
Example
Rows for orders created prior to July 1983 are deleted.[REV BEG]
DELETE WITH AUTOCOMMIT FROM PurchDB.Orders
WHERE OrderDate < '19830701'
[REV END]