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.
Example: Merging into a row-partitioned valid-time table
The following example uses a nontemporal source table for the merge. Note that the values to be inserted can be any values, even those that would go into a different partition.
CREATE MULTISET TABLE bi_tgt_tbl ( pkey_field INTEGER, int2_field INTEGER, vtcol PERIOD(DATE) NOT NULL AS VALIDTIME, ttcol PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME) PRIMARY INDEX ( pkey_field ) PARTITION BY CASE_N( ((END(vtcol)) >= DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)), ((END(vtcol)) < DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)), (END(ttcol )) < CURRENT_TIMESTAMP(6)); CREATE SET TABLE src_tbl ( pkey_field INTEGER, int2_field INTEGER) PRIMARY INDEX ( pkey_field ); SEQUENCED VALIDTIME MERGE INTO bi_Tgt_tbl USING /* This block prejoins and determines the target valid time values */ ( NONSEQUENCED VALIDTIME PERIOD (DATE'2009-12-15', DATE'2009-12-18') AND CURRENT TRANSACTIONTIME SELECT s.pkey_field, s.int2_field, END(b_t.vtcol) vtend, END(b_t.ttcol) ttend FROM src_tbl s LEFT OUTER JOIN bi_tgt_tbl b_t ON s.pkey_field = b_t.pkey_field ) AS nonbi_srct ( pkey, int2, vtend, ttend) ON (pkey_field = nonbi_srct.pkey) AND END(vtcol) = vtend AND END(ttcol) = ttend WHEN MATCHED THEN UPDATE SET int2_field = nonbi_srct.int2 WHEN NOT MATCHED THEN INSERT ( nonbi_srct.pkey, nonbi_srct.int2, PERIOD(TEMPORAL_DATE, UNTIL_CHANGED ) );