Neoview SQL Reference Manual (R2.2)

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.
SET Clause Restrictions and Error Cases
The SET clause has the following restrictions:
• The number of columns on the left side of each assignment operator should match the
number of values or SELECT list elements on the right side. The following examples are not
allowed:
UPDATE t SET (a,b)=(10,20,30)
UPDATE t set (b,c)=(SELECT r,t,s FROM x)
• If multi-column update syntax is specified and the right side contains a subquery, only one
element, the subquery, is not allowed.
UPDATE t SET (a,b)=(10, (SELECT a FROM t1))
172 SQL Statements