Compare two columns (date) and update new column with more recent date?

Question

How can I compare date column foo with the other date column bar and update column foobar with the more recent date.

ID        foo                    bar                foobar
1     '2014-01-23'         '0000-00-00'           '0000-00-00'
2     '0000-00-00'         '2013-03-01'           '0000-00-00'
3     '2013-03-03          '2014-04-04'           '0000-00-00'

score 2 · Answer 1 · answered Sep 04 '14 at 18:17

this statement updates foobar based on the condition in the case statement. Try something like this

UPDATE table_name  
SET     foobar =  CASE  
                        WHEN foo < bar THEN foo 
                        ELSE bar
                  END

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

I think you can also use GREATEST:

UPDATE table_name
   SET foobar = GREATEST(foo,bar);

edited May 23 '17 at 10:33

Community

1
1

answered Sep 04 '14 at 18:34

zerodiff

1,690
1
18
23

i actually ended up using this one, in the end. so i switched my vote. also think its cleaner – Dap Sep 26 '14 at 19:32

Compare two columns (date) and update new column with more recent date?

2 Answers2