CODEWITHSUNDEEP
mysqldatecompare
Dap
Dap

Reputation: 2359

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

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'

Upvotes: 0

Views: 89

Answers (2)

zerodiff
zerodiff

Reputation: 1700

I think you can also use GREATEST:

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

Upvotes: 2

Sam
Sam

Reputation: 2761

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

Upvotes: 2

Related Questions