DB2 SQL update more than 1 columns correlating with subquery

Question

I’m new in DB2 but not new in SQL and new a DB2 SQL update correlating with subquery in DB2 LUW V9.5. My Table is like below:
In rows 3 and 7 I need to update ACTIVE = 0 and VALID_TO = VALID_FROM from Rows 4 and 8 (SCD). I select data using below Statement:

SELECT T2.COL1, T2.COL1_HIST, T2.ACTIVE, T2.VALID_FROM, T2.VALID_TO, T3.VALID_FROM
FROM TMP.TABLE01 T2 INNER JOIN 
(
    SELECT COL1, MAX(COL1_HIST) MAX_HIST FROM TMP.TABLE01 WHERE 
    ACTIVE = 1
    GROUP BY COL1
    HAVING Count(*)>1
)T1 ON T2.COL1 = T1.COL1 AND T2.COL1_HIST < T1.MAX_HIST
INNER JOIN TMP.TABLE01 T3 ON T1.COL1 = T3.COL1 AND T1.MAX_HIST = T3.COL1_HIST
WHERE T2.ACTIVE = 1

To update in SQL world, I can use below statement:

UPDATE T2 SET T2.ACTIVE = 0,T2.VALID_TO=T3.VALID_FROM
FROM TMP.TABLE01 T2 INNER JOIN 
(
    SELECT COL1, MAX(COL1_HIST) MAX_HIST FROM TMP.TABLE01 WHERE 
    ACTIVE = 1
    GROUP BY COL1
    HAVING Count(*)>1
)T1 ON T2.COL1 = T1.COL1 AND T2.COL1_HIST < T1.MAX_HIST
INNER JOIN TMP.TABLE01 T3 ON T1.COL1 = T3.COL1 AND T1.MAX_HIST = T3.COL1_HIST
WHERE T2.ACTIVE = 1

How could this be in DB2 (DB2 LUW V9.5) world?
I know the similar Question in Stack Overflow, but it does not help me :( link

Many thanks in advance

Answer 1

Try this:

merge into table01 a using 
(
select rownumber() over(partition by col1 order by col1_hist desc) rn_, t.* 
from table01 t
where active=1
) m on m.rn_=1 and a.active=1 and a.col1=m.col1 and a.col1_hist<>m.col1_hist
when matched then update set active=0, valid_to=m.valid_from;

rn_ column of the inner subselect has value 1 for each row with the highest col1_hist in a group of records with the same col1.
We update all other active rows except these ones with correct valid_from value for each group.