The following table describes the modification of rows for a temporal merge:
| Temporal Merge Type | Modification Details |
|---|---|
| Current | A current merge results in a current update if matching rows are found; otherwise, it results in a current insert. The SET clause of the UPDATE portion cannot reference the valid-time column or the transaction-time column as the name of a column to update. The system may modify the temporal columns during an update of the matched row. For a row-partitioned table, if the modification of the temporal columns causes the rows to change partitions within the same AMP, the plan is sub-optimal since the row must be deleted and a new row inserted. The insert requires a spool. The specification of a valid-time value is allowed in the INSERT portion in the merge when the INSERT uses a named list or assignment list. This inserted row can be into a different partition from the matched partition within the same AMP. This can be achieved if the row to be inserted is spooled in the same way as done when the matched row changes partition. |
| Sequenced | If the source and target rows satisfy the matching condition for a sequenced merge, a sequenced update is performed.
If the source and target rows do not satisfy the matching condition, a sequenced insert is performed. The period of applicability, if specified, is ignored for the insert. The insert specification follows the rules for a simple sequenced insert and INSERT can specify a valid-time column value. The inserted row with the specified valid-time column value can be into a different partition from the matched partition within the same AMP. |
| Nonsequenced | If matching rows are found for a nonsequenced merge, a nonsequenced update is performed; otherwise, a nonsequenced insert is performed. The SET clause of the UPDATE portion can reference the valid-time column as the name of a column to update. For a row-partitioned table where partitioning is on the valid-time column, the valid-time column can be modified. The insert specification follows the rules of nonsequenced simple insert and the valid-time column value can be specified. The inserted row with the specified valid-time column value can be into a different partition from the matched partition within the same AMP. |