ALLBASE/SQL Reference Manual (36216-90216)

380 Chapter10
SQL Statements A - D
DELETE
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.
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.
DELETE WITH AUTOCOMMIT FROM PurchDB.Orders
WHERE OrderDate < '19830701'