The following table describes the fundamental constraints on an atomic upsert operation.
| Constraint | Explanation |
|---|---|
| The UPDATE and INSERT components of the upsert operation must specify the same table. | The purpose of an atomic upsert is to first attempt to update a particular row in a table and then, if that row is not found, to insert it into the table. By specifying different tables for the UPDATE and INSERT components of the statement, the intent of the operation is violated. |
| The table must have a primary index, which can be row partitioned but not column partitioned. | A primary index is needed to search for the row. |
| The UPDATE and INSERT components of the upsert operation must specify the same row. In other words, the primary index value for the inserted row is identical to the primary index value for the targeted update row. |
If an upsert operation cannot find the specific row it targets, then it inserts the row it would have otherwise updated, inserting the specific “update” values into the fields that would have been updated had the row existed in the table. This constraint is met when the primary index value specified by the WHERE clause of the UPDATE component matches the primary index value implied by the column values specified in the INSERT component. Because the value of the number generated for an INSERT into an identity column is not knowable in advance, you cannot perform an upsert operation into a target table that has an identity column as its primary index. Upserts into identity column tables for which the identity column is not the primary index are valid. |
| The UPDATE component fully specifies the primary index value to ensure that all accesses are one-AMP operations. | When the UPDATE component of the statement fully specifies the primary index, the system accesses any targeted row with a single-AMP hashed operation. This rule is applied narrowly in the Teradata Parallel Data Pump case, where it is taken to mean that the primary index is specified in the WHERE clause of the UPDATE component as equality constraints using simple constraint values (either constants or USING clause references to imported data fields). Simple values are also assumed for constraints on non-index columns and for the update values in the SET clause, avoiding any costly subqueries or FROM clause references. Similarly, TPump has a performance-driven preference to avoid subqueries in the INSERT. |
- The values of the partitioning columns must be specified in the WHERE clause of the UPDATE clause of the statement.
- The INSERT clause of the statement must specify the same partition as the UPDATE clause.
- The UPDATE clause must not modify a partitioning column.
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. |
| Teradata | transaction. |