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
)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…