17.05 - Example: Update a Row in a Valid-Time Table - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

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.