Search code examples
sqlteradatatemporal

Merge update Temporal

Existing data in table test : (Temporal table)

id name    valid_dt
1  cat     2012-06-16 - 9999-12-31

Incoming Table : (Temporal)

  id name  valid_dt
  1  bat   2013-12-28 - 9999-12-31

After Merge update , test table should have 

id name valid_dt
1 cat    2012-06-16 - 2013-12-28
1 bat    2013-12-28 - 9999-12-31

Will It be possible in Teradata Temporal Merge update statement if i run it today?

non working code i tried

SEQUENCED VALIDTIME 
MERGE INTO  test
USING    
(  
  sel * from incoming 
) H on id=H.id
when matched then
update  
set name = h.name
;

UPDATE After trying dnoeths solution: For delete :

In my real incoming table some records were actually closed .

so i need hard code end date as until_changed

non working delete :

sequenced validtime
delete from test where (cust_id,name) in
(nonsequenced validtime
 select id,name,period(begin(valid_dt),until_changed)
from incoming );
Solution

  • AFAIK you need to a two step process to maintain a ValidTime table like this, a Sequenced ValidTime DELETE followed by a NonSequenced ValidTime INSERT:

    SEQUENCED VALIDTIME 
DELETE test FROM incoming
WHERE test.id = incoming.id
;NONSEQUENCED VALIDTIME
INSERT INTO test SELECT * FROM incoming;

    Replacing the Insert/Select with Merge should be possible, but you still need the Delete first:

    NONSEQUENCED VALIDTIME 
MERGE INTO  test
USING    
(  
  sel * from incoming 
) H ON test.id=H.id
AND test.valid_dt = h.valid_dt
WHEN NOT MATCHED THEN
INSERT (H.id, H.name, h.valid_dt)

    Like non-temporal DML the MERGE might be more efficient than Insert/Select or Update.