For all MERGE statements, an ON clause match condition must specify an equality constraint on the primary index of the target table. (Note that MERGE is not supported on tables without primary indexes and column-partitioned tables.) To qualify for the ANSI MERGE path, which provides enhanced optimization, if the target table is row partitioned, the equality must also include the partitioning column to qualify a single partition. For information about the ANSI MERGE optimizer path, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Because the recommended partitioning expressions for temporal tables use only the END bound of the temporal column time periods, the match condition can similarly use the END condition in the equality constraint. END (valid_time_column) IS UNTIL_CHANGED and END (transaction_time_column) IS UNTIL_CLOSED can be used as equality constraints on temporal columns for temporal tables that use the recommended partitioning expressions (see Partitioning Expressions for Temporal Tables).
The ending bound of the valid-time of the target rows is seldom known in advance. A solution is to pre-join the source and target tables, using the same conditions in the USING clause, to determine the valid-time values in the target table.
The pre-join should use the NONSEQUENCED VALIDTIME qualifier (AND CURRENT TRANSACTIONTIME, if applicable). The pre-join must be a left outer join from the source in order to preserve the non-matching row set for insertion into the target table.