Merge with condition of input record freshness

Database
Teradata Employee

Merge with condition of input record freshness

Hi,

 

I need to achieve this kind of update else insert :

  • Update if fact exists on target and update timestamp of source row if greater than target (not 100% guaranteed that I'll receive fact records in the right order (yeah I know that sounds stupid))
  • (else) Insert if fact doesn't exist on target

Found 2 ways of doing so with merge (ID is PK):

  1.  One merge but heavy use of case when on the update statement :
MERGE INTO DB_TARGET.TARGET_TABLE
USING (
SELECT
s_ID,
s_UPD_TS,
s_ATTR
FROM DB_STAGING.SOURCE_TABLE
) S
ON 
DB_TARGET.TARGET_TABLE.ID = S.s_ID
WHEN MATCHED THEN UPDATE
SET
ATTR = CASE WHEN S.s_UPD_TS > UPD_TS THEN S.s_ATTR ELSE ATTR END,
UPD_TS = CASE WHEN S.s_UPD_TS > UPD_TS THEN S.s_UPD_TS ELSE UPD_TS END
WHEN NOT MATCHED THEN INSERT (
ID,
UPD_TS,
ATTR
)
VALUES ( 
S.s_ID,
S.s_UPD_TS,
S.s_ATTR);

2.  Two merge statements with different match clauses (1 for update then 1 for insert):

MERGE INTO DB_TARGET.TARGET_TABLE
USING (
SELECT
s_ID,
s_UPD_TS,
s_ATTR
FROM DB_STAGING.SOURCE_TABLE
) S
ON 
DB_TARGET.TARGET_TABLE.ID = S.s_ID
AND S.s_UPD_TS > UPD_TS
WHEN MATCHED THEN UPDATE
SET
ATTR = S.s_ATTR,
UPD_TS = S.s_UPD_TS;
MERGE INTO DB_TARGET.TARGET_TABLE
USING (
SELECT
s_ID,
s_UPD_TS,
s_ATTR
FROM DB_STAGING.SOURCE_TABLE
) S
ON 
DB_TARGET.TARGET_TABLE.ID = S.s_ID
WHEN NOT MATCHED THEN INSERT (
ID,
UPD_TS,
ATTR
)
VALUES ( 
S.s_ID,
S.s_UPD_TS,
S.s_ATTR);

 

Is there a 3rd way I failed to discover..?

 

thank you

1 REPLY
Teradata Employee

Re: Merge with condition of input record freshness

Another option, not necessarily better.

Pre-join the source and target in the USING clause to eliminate the "out of sequence" source rows:

MERGE INTO DB_TARGET.TARGET_TABLE
USING (
SELECT
S1.s_ID,
S1.s_UPD_TS,
S1.s_ATTR
FROM DB_STAGING.SOURCE_TABLE S1
LEFT JOIN DB_TARGET.TARGET_TABLE T1
ON S1.s_ID = T1.ID
WHERE T1.ID IS NULL OR S1.s_UPD_TS > T1.UPD_TS
) S
ON 
DB_TARGET.TARGET_TABLE.ID = S.s_ID
WHEN MATCHED THEN UPDATE
SET
ATTR = S.s_ATTR,
UPD_TS = S.s_UPD_TS
WHEN NOT MATCHED THEN INSERT (
ID,
UPD_TS,
ATTR
)
VALUES ( 
S.s_ID,
S.s_UPD_TS,
S.s_ATTR);