Rules for Updating Partitioning Columns of a Row-Partitioned Table - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
The following rules apply to updating the partitioning columns of a row-partitioned table:
  • For an UPDATE request that attempts to update the partitioning columns, the partitioning expression must result in a value between 1 and the number of partitions defined for that level.
  • For an UPDATE request that attempts to insert a row, the partitioning expression for that row must result in a value between 1 and the number of partitions defined for that level.
  • You cannot update the system-derived columns PARTITION and PARTITION#L1 through PARTITION#L62.
  • 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.
    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 update 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 partition that contains a row to be updated is determined by evaluating the partitioning expression on partitioning column values using the table’s session mode and collation.
  • In updating rows in 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 update a row in 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, the database aborts any attempts to update rows in the table and returns an error to the requestor.