[SOLVED] Update fields using Self Join

Update fields using Self Join - Teradata

I want to update CHAT_ACTIVITY_ID & CHAT_SMS_IND field in a table ABC.PERFORM_METRICS_F(History correction) using INTERACTION_SOURCE_KEY based on below criteria:

If CHAT_ACTIVITY_ID is NULL, update it with the CHAT_ACTIVITY_ID which is not null for that INTERACTION_SOURCE_KEY

Update CHAT_SMS_IND with the value that is present for not null CHAT_ACTIVITY_ID field. (Here for first example we will update 0 by 1 for INTERACTION SOURCE KEY - 21945) Primary Index of the table ABC.PERFORM_METRICS_F: METRIC_SOURCE_KEY, METRIC_SOURCE, CALENDAR_DATE

I have tried something like below:

UPDATE A
FROM 
(SEL * FROM ABC.PERFORM_METRICS_F WHERE CHAT_ACTIVITY_ID IS NULL) A, 
(SEL * FROM ABC.PERFORM_METRICS_F WHERE CHAT_ACTIVITY_ID IS NOT NULL) B
SET CHAT_ACTIVITY_ID = B.CHAT_ACTIVITY_ID, CHAT_SMS_IND = B.CHAT_SMS_IND
WHERE A.INTERACTION_SOURCE_KEY = B.INTERACTION_SOURCE_KEY
AND A.INTERACTION_SOURCE_KEY IN ('21945','22045','22847');

Sample Data:

        METRIC_SOURCE_KEY    METRIC_SOURCE  INTERACTION_SOURCE_KEY   CHAT_ACTIVITY_ID    CHAT_SMS_IND    CALENDAR_DATE                             EXPECTED RESULT(NOTE)

        21945                     3                21945               6534908765426         1             2022-05-29
        39827                     4                21945                     ?               0             2022-05-30      CHAT_ACTIVITY_ID & CHAT_SMS_IND should be carried down to this row
        22045                     3                22045               7345628390255         1             2022-06-15
        25430                     2                22045                     ?               0             2022-06-17      CHAT_ACTIVITY_ID & CHAT_SMS_IND should be carried down to this row
        22847                     3                22847               6427690875346         1             2022-06-06
        43216                     4                22847                     ?               0             2022-06-06      CHAT_ACTIVITY_ID & CHAT_SMS_IND should be carried down to this row
        49567                     2                22847                     ?               0             2022-06-07      CHAT_ACTIVITY_ID & CHAT_SMS_IND should be carried down to this row
        47289                     2                22847                     ?               0             2022-06-06      CHAT_ACTIVITY_ID & CHAT_SMS_IND should be carried down to this row

Solution

First, you want a subquery that returns a single row for each key, containing the values to apply to the rows with NULL. Then use that subquery as the source of the UPDATE. (Note that the target of the UPDATE should be a table not a subquery).

UPDATE A
FROM 
ABC.PERFORM_METRICS_F A, 
(SELECT INTERACTION_SOURCE_KEY, CHAT_ACTIVITY_ID, CHAT_SMS_IND
 FROM ABC.PERFORM_METRICS_F
 WHERE CHAT_ACTIVITY_ID IS NOT NULL
 /* Ensure only one row per INTERACTION_SOURCE_KEY */
 /* ORDER BY is required for ROW_NUMBER */
 QUALIFY ROW_NUMBER() OVER (PARTITION BY INTERACTION_SOURCE_KEY ORDER BY CALENDAR_DATE DESC) = 1
) B
SET CHAT_ACTIVITY_ID = B.CHAT_ACTIVITY_ID, CHAT_SMS_IND = B.CHAT_SMS_IND
WHERE A.INTERACTION_SOURCE_KEY = B.INTERACTION_SOURCE_KEY
AND A.CHAT_ACTIVITY_ID IS NULL;

If you are certain there is only one non-NULL row per key, then you don't need the QUALIFY clause in the subquery.