SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-114
MXCI Examples of DELETE
or utility operations with DML operations, can lead to inconsistent data within a
nonaudited table or between a nonaudited table and its indices.
To avoid problems, do not run DDL or utility operations concurrently with DML
operations on nonaudited tables. When you try to delete data in a nonaudited table
with an index, NonStop SQL/MX returns an error.
SET ON ROLLBACK Considerations
The SET ON ROLLBACK expression is evaluated when each row is processed during
execution of the DELETE statement. The results of the evaluation are applied when
and if the transaction is rolled back. This has two important implications:
•
If the SET ON ROLLBACK expression generates an error (for example, a divide by
zero or overflow error), the error is returned to the application when the DELETE
operation executes, regardless of whether the operation is rolled back.
•
If a DELETE operation is applied to a set of rows and an error is generated while
executing the DELETE operation, and the transaction is rolled back, the actions of
the SET ON ROLLBACK clause apply only to the rows that were processed by the
DELETE operation before the error was generated.
SET ON ROLLBACK Restrictions
The table must be audited. The columns used in the SET ON ROLLBACK clause:
•
Must be declared as NOT NULL.
•
Cannot be part of a referential integrity constraint or be part of a secondary index.
•
Cannot use the VARCHAR data type.
•
Cannot be used in the primary key, clustering key, or partitioning key.
MXCI Examples of DELETE
•
Remove all rows from the JOB table:
DELETE FROM persnl.job;
--- 10 row(s) deleted.
•
Remove the row for TIM WALKER from the EMPLOYEE table:
DELETE FROM persnl.employee
WHERE first_name = 'TIM' AND last_name = 'WALKER';
--- 1 row(s) deleted.
•
Remove from the table ORDERS any orders placed with sales representative 220
by any customer except customer number 1234:
DELETE FROM sales.orders
WHERE salesrep = 220 AND custnum <> 1234;
--- 2 row(s) deleted.
Pub/Sub
Pub/Sub