SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-317
Considerations for UPDATE
In general, to avoid conflicting updates on a row, use the SERIALIZABLE isolation
level. However, note that when you use SERIALIZABLE, you are limiting concurrent
data access.
Requirements for Data in Row
Each row to be updated must satisfy the constraints of the table or underlying base
table of the view. No column updates can occur unless all of these constraints are
satisfied. (A table constraint is satisfied if the check condition is not false—that is, it is
either true or has an unknown value.)
In addition, a candidate row from a view created with the WITH CHECK OPTION must
satisfy the view selection criteria. The selection criteria are specified in the WHERE
clause of the AS query-expression clause in the CREATE VIEW statement.
Reporting of Updates
When an UPDATE completes successfully, NonStop SQL/MX reports the number of
times rows were updated during the operation.
Under certain conditions, updating a table with indexes can cause NonStop SQL/MX to
update the same row more than once, causing the number of reported updates to be
higher than the actual number of changed rows. However, both the data in the table
and the number of reported updates are correct. This behavior occurs when all of
these conditions are true:

The optimizer chooses an alternate index as the access path.

The index columns specified in WHERE search-condition are not changed by
the update.

Another column within the same index is updated to a higher value (if that column
is stored in ascending order), or a lower value (if that column is stored in
descending order).
When these conditions occur, the order of the index entries ensures that
NonStop SQL/MX will encounter the same row (satisfying the same search-
condition) at a later time during the processing of the table. The row is then
updated again by using the same value or values.
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.