SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-239
Considerations for UPDATE
For example, suppose that the index of MYTABLE consists of columns A and B, and
the UPDATE statement is specified:
UPDATE MYTABLE
SET B = 20
WHERE A > 10;
If the contents of columns A and B are 11 and 12 respectively before the UPDATE,
after the UPDATE NonStop SQL/MX will encounter the same row indexed by the
values 11 and 20.
Updating Character Values
For a fixed-length character column, an update value shorter than the column length is
padded with single-byte ASCII blanks (HEX20) to fill the column. If the update value is
longer than the column length, string truncation of nonblank trailing characters returns
an error, and the column is not updated.
For a variable-length character column, an update value is not padded; its length is the
length of the value specified. As is the case for fixed length, if the update value is
longer than the column length, string truncation of nonblank trailing characters returns
an error, and the column is not updated.
In an SQL/MP entry-sequenced table, a value that updates a variable-length character
column must be the same length as the value it replaces.
Audited and Nonaudited Tables
SQL/MX tables must be audited. You can run NonStop SQL/MX against nonaudited
SQL/MP tables.
The Transaction Management Facility (TMF) product works only on audited tables, so
a transaction does not protect operations on nonaudited tables. Nonaudited tables
follow a different locking and error handling model than audited tables. Certain
situations such as DML error occurrences 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 UPDATE 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 UPDATE
operation executes, regardless of whether the operation is rolled back.
Pub/Sub