Neoview SQL Reference Manual (R2.5)

specify access options for the DML statements within a transaction, you override the isolation
level of the containing transaction. Each statement then executes with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION statement.
See “SET TRANSACTION Statement” (page 188).
Conflicting Updates in Concurrent Applications
If you are using the READ COMMITTED isolation level within a transaction, your application
can read different committed values for the same data at different times. Further, two concurrent
applications can update (possibly in error) the same column in the same row.
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, Neoview SQL reports the number of times rows were
updated during the operation.
Under certain conditions, updating a table with indexes can cause Neoview SQL 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 Neoview SQL 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 Neoview SQL 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 non blank 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 non blank trailing characters returns an error, and the column is not
updated.
UPDATE Statement 201