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

Postgresql – Update table between 2 max dates

I have a table with 5 columns.

  1. id
  2. bday
  3. update
  4. restartDate
  5. startDate

I need to update each rows update column with the most recent date between restartDate and startDate

I’ve tried:

update mytable
set update = (select MAX(v)from (values (restartDate),(startDate)) as value(v))

But that seems to make the update column current date(like now())

I should also mention that the startDate and restartDate can be null

For example:

restartDate startDate update
2022-11-11 null null

would set update as 2022-11-11

or

restartDate startDate update
2022-11-01 2022-11-05 null

would set update as 2022-11-05

restartDate cannot be null

Tags:

2

Answers


  1. 0

    You can use CASE for conditions.

    UPDATE mytable 
  SET update = CASE
    WHEN restartDate >= COALESCE(startDate,restartDate) THEN restartDate 
    ELSE startDate 
  END
    Login or Signup to reply.
  2. 0

    You can use GREATEST for that:

    UPDATE yourtable SET updated = GREATEST(restartDate, startDate);

    This will already be sufficient if you use Postgres as you said.

    Otherwise, if you use MariaDB or MYSQL, you will notice the column "updated" will be null if your startDate is null because their GREATEST doesn’t handle that. In this case, you can add a COALESCE:

    UPDATE yourtable 
SET updated = GREATEST(restartDate, COALESCE(startDate,restartDate));

    You can verify it’s working correctly here: db<>fiddle

    As you can also verify there is that the update command you’ve already mentioned is working, too.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search