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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-233
Considerations for UPDATE
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.
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.