Sql Synapse How to update the latest records

Table MAPPING

| ID | CUST_ID | MEMID | CRE_DT | UPD_DT
----------------------------------------
| 1  | AA21    |  NULL |20210101|20210101
| 2  | AA21    | MID01 |20210202|20210202
| 3  | AA50    | MID00 |20210101|20210101

Table MASTER

| ID | CUST_ID | NEW_MEMID
| 2  | AA21    | NW01
| 3  | AA50    | NW02

Output after update

Table MAPPING

| ID | CUST_ID | MEMID | CRE_DT | UPD_DT
----------------------------------------
| 1  | AA21    |  NULL |20210101|20210101
| 2  | AA21    | NW01  |20210202|20210202
| 3  | AA50    | NW02  |20210101|20210101

I have an issue to update data in Mapping, I’d like to update only latest records.

Note: 1 CUST_ID : M ID thus I need to partition by cust_id order by upd_dt DESC,ID ASC to get the latest records.

I’d like to update only rows

ID = 2 CUST_ID = AA21
ID = 3 CUST_ID = AA50


UPDATE MAPPING
SET MEMID = NEW_MEMID
FROM
    (SELECT DISTINCT 
         ID, CUST_ID, NEW_MEMID
     FROM MASTER) SRC
WHERE (SRC.ID, SRC.CUST_ID) IN 
     (SELECT ID, CUST_ID
      FROM 
          (SELECT 
               DISTINCT ID, CUST_ID,
               ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY COALESCE(UPDATED_DATE, CREATED_DATE) DESC, ID ASC) RN
           FROM MAPPING) LASTEST
      WHERE RN = 1)

Parse error at line: 33, column: 42: Incorrect syntax near ‘,’.

which is WHERE (SRC.ID,SRC.CUST_ID) IN

Answer

Assuming Azure Synapse is T-SQL compliant, you may use an updatable CTE here:

WITH cte AS (
    SELECT mp.MEMID, ms.NEW_MEMID,
           ROW_NUMBER() OVER (PARTITION BY mp.CUST_ID
                              ORDER BY COALESCE(mp.UPD_DT, mp.CRE_DT)) rn
    FROM MAPPING mp
    INNER JOIN MASTER ms ON ms.CUST_ID = mp.CUST_ID
)

UPDATE cte
SET MEMID = NEW_MEMID
WHERE rn = 1;

Advertisement

Answer