End Record in past time as incoming record start date [ TEMPORAL ]

Database
Enthusiast

End Record in past time as incoming record start date [ 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?

i am looking in 1 statement

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

;

Tags (2)
2 REPLIES
Enthusiast

Re: End Record in past time as incoming record start date [ TEMPORAL ]

dnoeth suggested delete and Merge insert . 

I am still looking in 1 update statement if its possible

Teradata Employee

Re: End Record in past time as incoming record start date [ TEMPORAL ]

Use SEQUENCED UPDATE or SEQUENCED MERGE-INTO. Below is the example

drop table vt1;

drop table incoming_vt1;

create multiset table vt1( id int, name char(10), valid_dt period(date) as validtime ); create multiset table incoming_vt1( id int, name char(10), valid_dt period(date) as validtime );

validtime insert into vt1(1,'cat', period(date'2012-06-16', until_changed)); validtime insert into incoming_vt1(1,'bat', period(date'2013-12-28', until_changed));

validtime sel * from vt1;

         id  name        VALIDTIME

-----------  ----------  ------------------------

          1  cat         ('12/06/16', '99/12/31')

validtime sel * from incoming_vt1;

         id  name        VALIDTIME

-----------  ----------  ------------------------

          1  bat         ('13/12/28', '99/12/31')

SEQUENCED VALIDTIME

MERGE INTO  vt1

USING   

(

  SEQUENCED VALIDTIME

  sel * from incoming_vt1

) H on vt1.id=H.id

when matched then

update

set name = h.name

;

validtime sel * from vt1;

         id  name        VALIDTIME

-----------  ----------  ------------------------

          1  bat         ('13/12/28', '99/12/31')

          1  cat         ('12/06/16', '13/12/28')

-- If incoming_vt table is non-temporal table then use NONSEQUENCED VALIDTIME <PA> in the USING clause.

-- Joined update should also work for this case. Below is example. If incoming_vt is non-temporal then convert to vt table first.

-- Joined UPDATE example

validtime update vt1 from incoming_vt1  h set name = h.name where vt1.id=h.id; validtime sel * from vt1;

         id  name        VALIDTIME

-----------  ----------  ------------------------

          1  bat         ('13/12/28', '99/12/31')

          1  cat         ('12/06/16', '13/12/28')

One restriction on MI - Target VT must contain source VT. MI semantics is: insert if-does-not-exist else update. The time portion for which the row exists is the target VT. The source for the update will say a VT in a sequenced update. This source VT in this release should not go outside the bounds of the target VT.