CODEWITHSUNDEEP
mysql
Gallardo994
Gallardo994

Reputation: 45

MySQL: Update multiple columns in IF statement

I'm trying to update several columns in case one new value is lower than the old one:

INSERT INTO `toptimes`.`$enc` (serial,name,time,date,tag) VALUES ('$serial','$name','$time','$date','$tag') ON DUPLICATE KEY UPDATE IF(`time` > '$time',(`time` = '$time',`name`='$name'));

Here comes the problem, I need to make it like that:

ON DUPLICATE KEY UPDATE IF(`time` > '$time',(`time` = '$time',`name`='$name'));

So, if new $time value is lower than the old time, update 2 columns: update time and also update name column, but if the case doesn't match, update nothing. How can I make it work?

Thanks.

Upvotes: 2

Views: 449

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use case expressions in the on duplicate key part. You just have to repeat the logic:

INSERT INTO `toptimes`.`$enc` (serial, name, time, date, tag)
    VALUES ('$serial', '$name', '$time', '$date', '$tag')
    ON DUPLICATE KEY UPDATE
        time = least(time, values(time)),
        name = (case when values(time) < time then values(name) else name end);

Upvotes: 3

Gallardo994
Gallardo994

Reputation: 45

Fixed it by moving time update to the end:

INSERT INTO `toptimes`.`$enc` (serial,name,time,date,tag) VALUES ('$serial','$name','$time','$date','$tag') ON DUPLICATE KEY UPDATE name = (CASE WHEN '$time' < `time` THEN '$name' ELSE `name` END), time = LEAST('$time',`time`);

Upvotes: 0

Related Questions