Question 114396 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to update value depends on latest date in postgres

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search