Neoview SQL Reference Manual (R2.2)

DELETE Statement
• “Syntax Description of DELETE”
• “Considerations for DELETE”
• “Examples of DELETE”
The DELETE statement is a DML statement that deletes a row or rows from a table or an updatable
view. Deleting rows from a view deletes the rows from the table on which the view is based.
DELETE does not remove a table or view, even if you delete the last row in the table or view.
The two forms of the DELETE statement are:
• Searched DELETE—deletes rows whose selection depends on a search condition
For the searched DELETE form, if there is no WHERE clause, all rows are deleted from the table
or view.
Searched DELETE is:
DELETE [NOMVLOG][WITH NO ROLLBACK] FROM table
| STREAM (table) [AFTER LAST ROW]
[SET ON ROLLBACK set-roll-clause [,set-roll-clause ]...]
[WHERE search-condition ]
[[FOR] access-option ACCESS]
set-roll-clause is:
column-name = expression
access-option is:
READ COMMITTED
| SERIALIZABLE
| REPEATABLE READ
| SKIP CONFLICT
Syntax Description of DELETE
NOMVLOG
specifies that the operation will not be recorded in the materialized view log table. Once this
option is used, a materialized view will no longer be consistent with its base tables and re
computing it produces a different result.
WITH NO ROLLBACK
specifies that the transaction within which the delete statement is executing does not rollback
if a transaction aborts. If you specify this option when AUTOCOMMIT is set to OFF an error
code is generated. If the AUTOCOMMIT option is set to OFF, you can use the SET
TRANSACTION statement to enable the NO ROLLBACK option for the transaction. See
“SET TRANSACTION Statement” (page 166).
An error message is generated if one of the following is true:
• WITH NO ROLLBACK used with STREAM clause
• WITH NO ROLLBACK used with SET ON ROLLBACK clause
table
names the user table or view from which to delete rows. table must be a base table or an
updatable view. To refer to a table or view, use the ANSI logical name:
See “Database Object Names” (page 196).
90 SQL Statements