Rules for Using MERGE With Row‑Partitioned Tables
The following rules apply to using the MERGE statement to insert rows into a row‑partitioned
primary index table or updating the columns of a partitioning expression.
The target table can be row partitioned. However, the target table cannot be column
partitioned.
For MERGE requests that update the partitioning columns of a table, a partitioning
expression must result in a value between 1 and the number of partitions defined for
that level.
For MERGE requests that insert a row into a table, the partitioning expression for
that row must result in a value between 1 and the number of partitions defined for
that level.
If you specify a Period column as part of a partitioning expression, then you can
only specify equality conditions on that Period column for a MERGE request. A MERGE
request that specifies inequality conditions on a Period column included in a partitioning
expression for the table returns an error.
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 a MERGE request.
If you specify a function that references BEGIN or END Period values in a partitioning
expression, an equality condition on that function is processed as a partitioning
value matching condition.
If you specify a function that references a BEGIN and an END Period in a partitioning
expression, the system processes the equality condition on the BEGIN and END as a
partitioning matching condition.
Such a request must result in a single partition.
The system processes the conditions IS UNTIL_CHANGED and IS UNTIL_CLOSED as equality
conditions for the function that references the END Period only. See Temporal Table Support for more information.
The INSERT clause must specify the same partitioning column values as the match condition.
This rule also applies when the matching condition specifies a function that references
a Period.
You cannot update the system‑derived columns PARTITION and PARTITION#L1 through PARTITION#L62.
You cannot insert either a value or a null into any of the system‑derived PARTITION
columns.
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.
For the merge operation to succeed, the session mode and collation at the time the
table was created do not need to match the current session mode and collation. This
is because the row partition in which a row is to be inserted or updated is determined
by evaluating the partitioning expression on partitioning column values using the
table’s session mode and collation.
Collation has the following implication for merging rows into a table defined with
a character partitioning. If the collation for the table is either MULTINATIONAL or
CHARSET_COLL and the definition for the collation has changed since the table was
created, the system aborts any request that attempts to merge a row into the table
and returns an error to the requestor.
If the partitioning expression for a table 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 insert rows into the table
and returns an error to the requestor.