Other Restrictions on Atomic Upsert Feature
Some of these restrictions concern syntax that is supported in UPDATE and INSERT statements
separately, but not when combined in an Atomic upsert statement. Other restrictions
concern the upsert feature's not supporting certain Teradata Database features such
as triggers and join/hash indexes, meaning that the upsert statement cannot be used
on any table utilizing those features.
The following restrictions are not supported by the Atomic upsert feature, and return
an error if submitted to Teradata Database:
INSERT … SELECT: Syntax not supported. The INSERT may not use a subquery to specify
any of the inserted values. Note that support of this syntax is likely to be linked
to support of subqueries in the UPDATE's WHERE clause constraints as described above,
and may involve new syntax features to allow the UPDATE and INSERT to effectively
reference the same subquery.
UPDATE‑WHERE‑CURRENT: Syntax not supported. The WHERE clause cannot use an updatable cursor to do what
is called a positioned UPDATE. (It is unlikely that this syntax will ever be supported.) Note that this
restriction does not prevent cursors from being used in other ways with Atomic upsert
statements. For example, a DECLARE CURSOR statement may include upsert statements
among those to be executed when the cursor is opened, as long as the upserts are otherwise
valid.
UPDATE‑FROM: Not supported. The SET clause cannot use a FROM clause table reference in the expression
for the updated value for a column.
UPDATE‑WHERE SUBQUERIES: Not supported. The WHERE clause cannot use a subquery either to specify the primary
index or to constrain a nonindex column. Note that supporting this UPDATE syntax would
also require support for either INSERT … SELECT or some other INSERT syntax feature
that lets it specify the same primary index value as the UPDATE.
UPDATE‑PRIMARY INDEX: Not supported. The UPDATE cannot change the primary index. This is sometimes called
unreasonable update.
TRIGGERS: Feature not supported if either the UPDATE or INSERT could cause a trigger to be
fired. The restriction applies as if the UPDATE and INSERT were both executed, because
the parser trigger logic will not attempt to account for their conditional execution.
UPDATE triggers on columns not referenced by the UPDATE clause, however, will never
be fired by the upsert and are therefore permitted. DELETE triggers cannot be fired
at all by an upsert and are likewise permitted. Note that an upsert could be used
as a trigger action but it would be subject to the same constraints as any other upsert.
Because an upsert is not allowed to fire any triggers itself, an upsert trigger action
must not generate any further cascaded trigger actions.
JOIN/HASH INDEXES: Feature not supported if either the UPDATE or INSERT could cause the join/hash index
to be updated. As with triggers, the restriction applies to each upsert as if the
UPDATE and INSERT were both executed. While the UPDATE could escape this restriction
if the join/hash index does not reference any of the updated columns, it is much less
likely (and maybe impossible) for the INSERT to escape. If the benefit of lifting
the restriction for a few unlikely join/hash index cases turns out to be not worth
the implementation cost, the restriction may have to be applied more broadly to any
table with an associated join/hash index.
Treat the failed constraint as a nonfatal error, report the error in the job log for
diagnostic purposes, and continue with the job by reverting to the old non‑Atomic
upsert protocol.