How to update value depends on latest date in postgres-CodePudding

I have table like follows

col date
a   2022-07-26
a   2022-07-27
c   2022-08-02
d   2022-07-28

and I would like to update value at latest date column

update table
set date = '2022-09-30'
where col='a'
and // some logic

my desired result is as follows

col date        
a   2022-07-26   
a   2022-09-30
c   2022-08-02
d   2022-07-28

how to select latest days and update ?

Thanks

CodePudding user response：

We can use an update join approach here:

UPDATE yourTable t1
SET date = '2022-09-30'::date
FROM (
    SELECT col, MAX(date) AS max_date
    FROM yourTable
    GROUP BY col
) t2
WHERE t2.col = t1.col AND
      t2.max_date = t1.date AND
      t1.col = 'a';

The subquery above aliased as t2 will target only the latest date record for each col group. We then set the date for that record, for col = a.

CodePudding user response：

An easy and readable approach would be to simply update the record for the MAX(date).

UPDATE table 
   SET date = '2022-09-30' 
 WHERE col = 'a' 
   AND date = (SELECT MAX(date) FROM table WHERE col = '0');

This is assuming you only want to update for a, and for this 1 record. If you want to update for each 'col' on the highest date, you for example use a CTE;

WITH CTE_max_dates AS (SELECT col, max(date) FROM table)
UPDATE t
   SET date = '2022-09-30'
  FROM table t
  JOIN CTE_max_dates cte
    ON t.col = cte.col
   AND t.date = cte.date