UPDATE (Upsert Form) Insert Operations and Row-Partitioned Tables - Teradata Database

UPDATE (Upsert Form) Insert Operations and Row-Partitioned Tables

The rules for the INSERT component of the Upsert form of an UPDATE statement for a target row-partitioned table are as follows:

The target table can be row partitioned but cannot be column partitioned.

The outcome of partitioning expression evaluation errors, such as divide‑by‑zero errors, depends on the session mode.

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.

To insert a row, the partitioning expression must produce a partition number that results in a value between 1 and 65,535 (after casting to INTEGER, if it does not have an INTEGER data type).

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.

A Period column in an upsert request must be a term in an equality condition.

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. If you submit an upsert request that specifies inequality conditions on a Period column that is specified in a partitioning expression for the table, then Teradata Database aborts the request and returns an error.

However, you can specify a Period column that is not defined as part of a partitioning expression for both equality and inequality conditions on that column for an upsert request.

If a partitioning expression specifies a Period bound function, then an equality condition on the bound function is treated as a partitioning bound matching condition.

If both BEGIN and END bounds are specified in a partitioning expression of the table, then only an equality condition on both bounds is treated 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.

Collation mode has the following implications for upsert operations that insert rows into tables defined with a character partitioning:

If the collation for a row-partitioned table is either MULTINATIONAL or CHARSET_COLL and the definition for the collation has changed since the table was created, Teradata Database aborts any request that attempts to insert or update a row in the table and returns an error to the requestor.