CODEWITHSUNDEEP
mysqlmax
Gennaro Santoro
Gennaro Santoro

Reputation: 31

Mysql Update field with max value between 2 other fields

In my mysql table i have 3 fields: weight, gweight, volweight

gweight and volweight have different values, i'd like to update weight with max value between gweight and volweight. Thanks

Upvotes: 0

Views: 788

Answers (1)

Drew
Drew

Reputation: 24960

UPDATE myTable
SET weight=GREATEST(gweight,volweight);

Warning this will update every row.

See the manual page for GREATEST().

Other things to note:

CREATE TABLE xxx2
(   id int auto_increment primary key,
    col1 int null,
    col2 int null,
    col3 int null
);

INSERT xxx2(col1,col2) values (null,1),(1,2);

UPDATE xxx2
SET col3=GREATEST(col1,col2);
SELECT * FROM xxx2;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | NULL |    1 | NULL |
|  2 |    1 |    2 |    2 |
+----+------+------+------+

So NULL in a column does not make for a happy GREATEST(). If your columns are not NULLABLE then there is no worry for the above and you can ignore the below fix:

TRUNCATE xxx2;
INSERT xxx2(col1,col2) values (null,1),(1,2);

UPDATE xxx2
SET col3=GREATEST(COALESCE(col1,0),COALESCE(col2,0));
SELECT * FROM xxx2;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | NULL |    1 |    1 |
|  2 |    1 |    2 |    2 |
+----+------+------+------+

So COALESCE() would fix NULL issues.

Upvotes: 1

Related Questions