动态更新sql

SELECT  id,project_name, verify_time FROM "b_monitoring_date" where type ='0'  ORDER BY project_name, verify_time desc


SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY project_name ORDER BY verify_time DESC) AS rn
    FROM b_monitoring_date where type ='0'
) subquery WHERE rn = 1;

update b_monitoring_date set verify_time = tt2.newtime   from  (select  replace(to_char(tt1.verify_time, 'yyyy-mm-dd hh24:mi:ss'),to_char(tt1.verify_time, 'YYYY-MM'),'2024-01')::timestamp newtime,*  from (SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY project_name ORDER BY verify_time DESC) AS rn
    FROM b_monitoring_date where type ='0'
) subquery WHERE rn = 1) tt1) tt2 where b_monitoring_date.id = tt2.id