17.10 - Example: Update a Row in a Valid-Time Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

Assume an insurance customer has a basic homeowner’s policy begin on February 3, 2007, which is valid and renewed automatically unless the policy holder requests a change. The full information, including the period of validity for the customer’s policy (in the Policy_Term column) is shown by the following query:

NONSEQUENCED VALIDTIME SELECT * FROM Policy WHERE Policy_ID=540944;
 Policy_ID  Customer_ID  Policy_Type  Policy_Details        Policy_Term
----------  -----------  -----------  --------------------  ------------------------
    540944    123344567  HM           STD-PL-332-YXY-01     ('07/02/03', '99/12/31')

Now assume that the customer requests after a year that the policy be upgraded to a premium homeowner’s policy. The update statement specifies a period of applicability for the change, which would be from the day of the change, February 3. 2008, indefinitely again, until the customer requests further changes or cancels the policy. The following statement would make the required update to the customer policy:

SEQUENCED VALIDTIME PERIOD '(2008-02-03, UNTIL_CHANGED)'
UPDATE POLICY SET Policy_Type='HP'
WHERE Policy_ID=540944;

The result will be two rows in the table for this Policy_ID:

  • a history row showing the initial policy with the Policy_Term column (the valid-time column) showing an ending date corresponding to when the change was made
  • a row reflecting the current policy, with valid time starting on the date the change was made
    NONSEQUENCED VALIDTIME SELECT * FROM Policy where Policy_ID=540944;
    
      Policy_ID  Customer_ID  Policy_Type  Policy_Details      Policy_Term
    -----------  -----------  -----------  ------------------- ------------------------
         540944    123344567  HP           STD-PL-332-YXY-01   ('08/02/03', '99/12/31')
         540944    123344567  HM           STD-PL-332-YXY-01   ('07/02/03', '08/02/03')

For a nontemporal table, using nontemporal semantics, the Policy_Type value in the row would have simply been replaced, with no history row left in the table to show how the policy had existed prior to the change.