Updating a table column with oldest date in each row, except where the oldest date has already passed?

Question

I would like to update the contents of the Date1 column to reflect the oldest date in each row, unless the date has already passed (Date1 < current date), in which case i'd like Date1 to be populated with the 2nd oldest date in the row.

For added context, The table has thousands of rows and ~15 columns, only a handful of which are dates. I've used the least function in the past to update this column with the oldest date in each row, but I can't figure out how to update it with the 2nd oldest date if the oldest date is prior to the current date.

ID	Date 1	Date 2	Date 3	Date 4
001		01/14/2022	01/15/2022	01/16/2022
002		04/15/2019	03/20/2021	06/16/2021

Answer 1

You want the oldest date today or later. You can do this without a subquery, using least() and conditional expressions:

update mytable t
set date1 = least(
    case when date2 >= current_date then date2 end,
    case when date3 >= current_date then date3 end,
    case when date4 >= current_date then date4 end
)

The case expression turns "past" dates to null, which least() ignores.

An alternative unpivots the columns to rows and then uses filtering and aggregation:

update mytable t
set date1 = (
    select min(date)
    from (values (date2), (date3), (date4)) d(dt)
    where dt >= current_date
)