ALLBASE/SQL Reference Manual (36216-90216)

378 Chapter10
SQL Statements A - D
DELETE
DELETE
The DELETE statement deletes a row or rows from a table.
Scope
ISQL or Application Programs
SQL Syntax
DELETE [WITH AUTOCOMMIT]FROM {[ Owner. ] TableName
[ Owner. ] ViewName } [WHERE SearchCondition
Parameters
WITH AUTOCOMMIT executes a COMMIT WORK automatically at the beginning of the
DELETE statement and also after each batch of rows is deleted.
[
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 .