[ { CURRENT | NONSEQUENCED } VALIDTIME |
[ SEQUENCED ] VALIDTIME [ period_expression ] |
NONTEMPORAL
]
upsert_statement [;]
- CURRENT VALIDTIME
- Specifies that the upsert is current in the valid-time dimension.
The CURRENT VALIDTIME qualifier is used to qualify rows from the referenced tables in the valid-time dimension. In the transaction-time dimension, open rows qualify.
If the session temporal qualifier is not set and the temporal qualifier is omitted from the upsert statement, the default qualifier is CURRENT VALIDTIME.
For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.
CURRENT DML modifications can cause serializability issues for concurrent transactions. See Potential Concurrency Issues with Current Temporal DML for information on avoiding these issues.
- VALIDTIME and SEQUENCED VALIDTIME
- Specifies that the upsert is sequenced in the valid-time dimension if the target table supports valid time.
The target table must have a valid-time column.
A sequenced upsert on a target table that supports valid time sets the valid-time value to period_expression, or, if omitted, to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP) valid-time column.
For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.
- period_expression
-
Specifies the period of applicability for the DML statement.
The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.
The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.
If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP( n ) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.
- NONSEQUENCED VALIDTIME
- Specifies that the upsert is nonsequenced in the valid-time dimension if the target table supports valid time.
The target table must have a valid-time column.
For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.
- upsert_statement
- Specifies existing UPDATE (Upsert Form) statement syntax.
For details, see SQL Data Manipulation Language.
The following restrictions apply to the SET clause of the UPDATE portion of upsert_statement:- For a current or sequenced update on a table with valid time, the SET clause cannot reference the valid-time column as the name of the column whose data is to be updated.
- For an update on a table with transaction time, the SET clause cannot reference the transaction-time column as the name of the column whose data is to be updated.
- The SET clause cannot use CURRENT_DATE or CURRENT_TIMESTAMP in the expressions that are used to assign a value to the valid-time column.
- For a row-partitioned table, the SET clause cannot reference a partitioning column