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.