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 |
Advertisement
Answer
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 )