Neoview SQL Reference Manual (R2.3)

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][long-running-update-option] 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
long-running-update-option is:
WITH NO ROLLBACK
| WITH MULTI COMMIT
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 MULTI COMMIT
forces the delete operation to be run in multiple statements. Each one of these multiple
statements is run in a multiple transactions.
The access-option specified in conjunction with MULTI COMMIT takes effect for each
of these multiple transactions.
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
DELETE Statement 91