-1

I would like to update the contents of the Date 1 column to reflect 2nd oldest date each row. The table has thousands of rows and ~15 columns, only a handful of which are dates. I've used the least funtion 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(1/15/2020 for row 1 and 04/15/2020 for row 2 in this instance). Any help would be greatly appreciated.

ID Date 1 Date 2 Date 3 Date 4
001 01/14/2020 01/15/2020 01/16/2020
002 04/15/2020 03/20/2020 06/16/2020
GMB
  • 216,147
  • 25
  • 84
  • 135
Tboi
  • 57
  • 5

1 Answers1

0

The simplest option might be to unpivot:

select t.*, d.date
from mytable t
cross join lateral (
    select date
    from (values (t.date2), (t.date3), (t.date4)) d(date)
    order by d.date
    limit 1 offset 1
) d

If you wanted an update statement:

update mytable t
set date1 = (
    select date
    from (values (date2), (date3), (date4)) d(date)
    order by d.date
    limit 1 offset 1
)

Demo on DB Fiddle

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you so much. The people who answer questions here are heroes. – Tboi Dec 29 '20 at 18:50
  • What if I wanted to update the the Date 1 field with the oldest date in the row (update mytable set date1 = least(date 2, date3, date4); select * from mytable) , but update Date 1 with the 2nd oldest only where the oldest date in the row has already passed (i.e. Date 1 – Tboi Dec 30 '20 at 17:04
  • if it is preferred that I submit a new question, I can do this too. – Tboi Dec 30 '20 at 17:08
  • @Tboi: yes, you might want to ask a new question for this. – GMB Dec 30 '20 at 17:23