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:

would set update as 2022-11-11

or

would set update as 2022-11-05

restartDate cannot be null

CodePudding user response：

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.

CodePudding user response：

You can use CASE for conditions.

UPDATE mytable 
  SET update = CASE
    WHEN restartDate >= COALESCE(startDate,restartDate) THEN restartDate 
    ELSE startDate 
  END