- For an UPDATE request that attempts to insert or update a row in a base table that causes an insert into a join index with row partitioning, the partitioning expression for that index row must result in a value between 1 and the number of partitions defined for that level.
- For an UPDATE request that attempts to insert or update a row in a base table that causes an update of an index row in a join index with row partitioning, the partitioning expression for that index row after the update must result in a value between 1 and the number of partitions defined for that level.
- Updating a base table row does not always cause inserts or updates to a join index on that base table. For example, you can specify a WHERE clause in the CREATE JOIN INDEX statement to create a sparse join index for which only those rows that meet the condition of the WHERE clause are inserted into the index, or, for the case of a row in the join index being updated in such a way that it no longer meets the conditions of the WHERE clause after the update, cause that row to be deleted from the index. The process for this activity is as follows:
- The system checks the WHERE clause condition for its truth value after the update to the row.
Condition Result Description FALSE System deletes the row from the sparse join index. TRUE System retains the row in the sparse join index and proceeds to stage b. - The system evaluates the new result of the partitioning expression for the updated row.
Partitioning Expression Result - evaluates to null, or
- is an expression that is not CASE_N or RANGE_N
Not between 1 and 65535 for the row. The system aborts the request. It does not update the base table or the sparse join index, and returns an error.
- evaluates to a value, and
- is an expression that is not CASE_N or RANGE_N
Between 1 and 65535 for the row. The system stores the row in the appropriate partition, which might be different from the partition in which it was previously, and continues processing requests.
- The system checks the WHERE clause condition for its truth value after the update to the row.
- In updating rows in a table defined with a character partitioning, if a noncompressed join index with a character partitioning under either an MULTINATIONAL or CHARSET_COLL collation sequence is defined on a table and the definition for the collation has changed since the join index was created, Teradata Database aborts any request that attempts to update a row in the table and returns an error to the requestor whether the update would have resulted in rows being modified in the join index or not.
- If the partitioning expression for a noncompressed join index involves Unicode character expressions or literals and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to update rows in the table and returns an error to the requestor.
- You cannot update a base table row that causes an insert into a join index with row partitioning such that a partitioning expression for that index row does not result in a value between 1 and the number of row partitions defined for that level.
- You cannot update a base table row that causes an update of an index row in a join index with row partitioning such that a partitioning expression for that index row after the update does not result in a value between 1 and the number of row partitions defined for that level.