CODEWITHSUNDEEP
mysql
kaklon
kaklon

Reputation: 2432

mysql how to update a field only if new value is lower then the existing value

I'd like to update the value of a field only if the new one is lower. I tried min like this, without success

UPDATE `editions` SET `editions`.`year`  = MIN(`editions`.`year` , '2005')  WHERE `editions`.`id` = 1;

I get a 1064 error. How can I update the value without retrieving the value first?

Upvotes: 1

Views: 395

Answers (2)

Mat
Mat

Reputation: 206729

Have you tried doing it like this:

update editions set year = 2005 where id = 1 and year > 2005;

(i.e. restrict the selection to row(s) that you would want updated)

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56357

UPDATE `editions` 
SET `editions`.`year` = 
LEAST(`editions`.`year` , '2005')  WHERE `editions`.`id` = 1;

Upvotes: 4

Related Questions