The rules for the UPDATE component of the Upsert form of an UPDATE statement in a target row-partitioned table are as follows.
The target table must have a primary index and can be row partitioned but cannot be column partitioned.
You cannot submit an UPDATE request to update the partitioning columns of a table with row partitioning such that a partitioning expression does not result in a value between 1 and the number of partitions defined for that level.
You must specify the same partition of the combined partitioning expression for the INSERT component in the upsert form of an UPDATE request that you specify for the UPDATE component.
You must specify the values of the partitioning columns in the WHERE clause of the upsert form of an UPDATE request.
You also cannot modify the values of those partitioning columns; otherwise, the system aborts the request and returns an error message to the requestor.
You cannot update the system-derived columns PARTITION and PARTITION#L1 through PARTITION#L62.
Expression evaluation errors, such as divide by zero, can occur during the evaluation of a partitioning expression. The system response to such an error varies depending on the session mode in effect at the time the error occurs.
| In this session mode … | Expression evaluation errors roll back this work unit … |
|---|---|
| ANSI | request that contains the aborted request. |
| Teradata | transaction that contains the aborted request. |
Take care in designing your partitioning expressions to avoid expression errors.
The INSERT clause must specify the same partitioning column values as the UPDATE clause. This also applies when the UPDATE condition specifies conditions based on Period bound functions.
If you specify a Period column as part of the partitioning expression, you can only specify equality conditions on that column for an upsert request on that table. An upsert request cannot specify inequality conditions on a Period column that is specified in a partitioning expression for the table.
However, an upsert request can specify a Period column that is not defined as part of a partitioning expression for equality and inequality conditions on that column.
If you specify a Period column in an upsert request, it must be a term in an equality condition.
If the partitioning expression for a target table specifies a Period bound function, then an equality condition on the bound function is treated as a partitioning bound matching condition.
If BEGIN and END bounds are specified in the partitioning expression of the table, only an equality on both bounds is processed as a partitioning bound matching condition. This must result in a single partition.
The conditions IS UNTIL_CHANGED and IS UNTIL_CLOSED are treated as equality conditions only for the END bound function.