MySQL - Update column with MAX or GREATEST Value from fields in row (excluding NULL)

In MySQL in the table mytable I want to UPDATE column max_value

Example before the query the table looks like:

___________________________________________________
|| id || value1 || value2 || value3 || max_value ||
___________________________________________________
|| 1  ||    1   ||   2    ||   3    ||   NULL    ||
===================================================
|| 2  ||    99  ||   2    ||   3    ||   NULL    ||
===================================================
|| 3  ||    1   ||   66   ||   66   ||   NULL    ||
===================================================
|| 4  ||    1   ||   2    ||  NULL  ||   NULL    ||
===================================================
|| 5  ||  NULL  ||  NULL  ||  NULL  ||   NULL    ||
___________________________________________________

Ater the SQL statement max_value should have been updated with the highest value of value1, value2 or value3. Like this:

___________________________________________________
|| id || value1 || value2 || value3 || max_value ||
___________________________________________________
|| 1  ||    1   ||   2    ||   3    ||     3     ||
===================================================
|| 2  ||    99  ||   2    ||   3    ||     99    ||
===================================================
|| 3  ||    1   ||   66   ||   66   ||     66    ||
===================================================
|| 4  ||    1   ||   2    ||  NULL  ||     2     ||
===================================================
|| 5  ||  NULL  ||  NULL  ||  NULL  ||   NULL    ||
___________________________________________________

Can anyone give me a MySQL query for this?

Answers


GREATEST doesn't interact with NULL in the way you need, but an UPDATE/JOIN will do it without too much complexity;

UPDATE Table1 t1
JOIN (
  SELECT id,MAX(value) value FROM (  
    SELECT id,value1 value FROM Table1 UNION ALL 
    SELECT id,value2       FROM Table1 UNION ALL
    SELECT id,value3       FROM Table1
  ) g GROUP BY id
) a ON t1.id = a.id
SET t1.max_value=a.value;

An SQLfiddle to test with.


You have to replace NULL values with 0 or any value you like, else the comparision with an "unknown value" always returns an "unknown value".

update table1 
set max_value = greatest(coalesce(value1, 0), coalesce(value2, 0), coalesce(value3, 0));

The coalesce() function returns the first of its parameters which is not null


Need Your Help

Template method enable_if specialization

c++ class templates c++11 enable-if

i have following code that does not compile.

Finding the first row in a group using Hive

group-by hive hiveql

For a student database in the following format

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.