UPDATE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - UPDATE (ANSI Valid-Time Table Form) - Advanced SQL Engine

Purpose

Modifies one or more existing rows in valid-time tables with the option of modifying the rows for only a portion of their valid-time periods.

Syntax

UPDATE valid_time_table
  [ FOR PORTION OF valid_time_period_name
      FROM point_in_time_1 TO point_in_time_2
  ]
  SET set_clause_list
  [ WHERE search_condition ] [;]

valid_time_table: The valid time table in which you are modifying rows.
FOR PORTION OF: Qualifies rows to be modified that have valid-time periods that are within or that overlap the period defined by point_in_time_1 and point_in_time_2.
valid_time_period_name: The name of the valid-time derived period column. This is not “VALIDTIME.” It is the name assigned to the derived period column when the table was defined.
point_in_time_1
point_in_time_2: Delimits the period of applicability of the modification.; A date or timestamp expression that can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a DATE or TIMESTAMP[(n)] [WITH TIME ZONE] value.; The expression can be any expression, including parameterized values and built-in functions such as CURRENT_DATE, CURRENT_TIMESTAMP, TEMPORAL_DATE, or TEMPORAL TIMESTAMP. The expression cannot reference any columns, but it can be a self-contained noncorrelated scalar subquery.
SET set_clause_list
WHERE search_condition: For information on the this syntax and other nontemporal keywords, clauses, and options for UPDATE, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
The SET clause cannot include the start or end column components of the valid time period.

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

Usage Notes

You cannot use the FOR PORTION OF qualifier within a MERGE INTO statement.

Examples of Updating Rows in ANSI Valid-Time Tables

For the following examples, assume the queries are issued against the following valid-time table named employee_vt that contains a mix of current, future, and history rows:

 eid ename terms   job_start     job_end
---- ----- ----- ----------- -----------
1002 Ash    TA05  2003/01/01  2003/12/31
1005 Alice  TW10  2004/12/01  9999/12/31
1010 Mike   TW07  2015/01/01  2016/12/31
1001 Sania  TW08  2002/01/01  2006/12/31
1004 Fred   PW12  2001/05/01  9999/12/31
1003 SRK    TM02  2004/02/10  2005/02/09

Example: Simple UPDATE on an ANSI Valid-Time Table

A simple UPDATE statement without temporal qualification operates just as an UPDATE on a nontemporal table, completely UPDATING rows that qualify for deletion:

UPDATE employee_vt SET terms='TA07' WHERE ename='Ash';


SELECT * FROM employee_vt;

 eid ename terms   job_start     job_end
---- ----- ----- ----------- -----------
1002 Ash    TA07  2003/01/01  2003/12/31
1005 Alice  TW10  2004/12/01  9999/12/31
1010 Mike   TW07  2015/01/01  2016/12/31
1001 Sania  TW08  2002/01/01  2006/12/31
1004 Fred   PW12  2001/05/01  9999/12/31
1003 SRK    TM02  2004/02/10  2005/02/09

Example: UPDATE on an ANSI Valid-Time Table Where the PA of Update Overlaps a Portion of the PV of the Row

Temporal tables allow you to modify rows for only a portion of their period of validity. The database takes care of adding rows and adjusting valid-time periods to account for the change automatically. The database automatically handles the valid-time modifications for the row, which may involve changing the period bounds and adding new rows to the table.

For example, assume the company promotes Alice from employment terms TW10 to PW11 after she has been working for a year. Modifying the Alice row for only that portion of the row period of validity automatically yields two rows for Alice in the table:

UPDATE employee_vt
FOR PORTION OF job_dur FROM DATE'2005-12-01' TO DATE'9999-12-31'
SET terms='PW11'
WHERE ename='Alice';

SELECT * FROM employee_vt WHERE ename=’Alice’;

eid ename terms    job_start     job_end
---- ----- ----- ----------- -----------
1005 Alice PW11   2005/12/01  9999/12/01
1005 Alice TW10   2004/12/01  2005/12/01

Now the table has two rows for Alice that show how the terms of her employment changed, and the temporal extent of when she worked under each of the terms.

Example: UPDATE on an ANSI Valid-Time Table Where PA of Update is Within PV of Row

If the PV of the update lies within the PA of a row, only the portion of the row information that is valid during the overlap is updated, while the portions of row that existed before and after the change remain in the table as separate rows. Assume that Fred has to reduce his work for the company for the year of 2005, and agrees to a change in the terms of his contract for that period:

UPDATE employee_vt
FOR PORTION OF job_dur FROM DATE'2005-01-01' TO DATE'2006-01-01'
SET terms='TW10'
WHERE ename='Fred';

SELECT * FROM employee_vt WHERE ename=’Fred’;

 eid ename term    job_start     job_end
---- ----- ----- ----------- -----------
1004 Fred  TW10   2005/01/01  2006/01/01
1004 Fred  PW12   2001/05/01  2005/01/01
1004 Fred  PW12   2006/01/01  9999/12/31

Where there had been one row for Fred in the table before the UPDATE, after the UPDATE there are three rows. The three rows track the changing terms of Fred’s employment, starting with terms PW12, changing to TW10 for one year beginning in 2005, then returning to PW12 in 2006.

Because this was a valid-time UPDATE executed against a valid-time table, all the work of creating new rows and adjusting the valid-times for each row was handled automatically by the database.

UPDATE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - UPDATE (ANSI Valid-Time Table Form) - Advanced SQL Engine - Teradata Database

ANSI Temporal Table Support