on duplicate key update if with multiple fields

Question

INSERT INTO highscore(user, points, modality, time, level)
VALUES('$user', '$score', '$modality', '$time', '$level')

ON DUPLICATE KEY UPDATE
  points = IF(VALUES(points) > points, VALUES(points), points),
  time = IF(VALUES(points) > points, VALUES(time), time),
  level = IF(VALUES(points) > points, VALUES(level), level)

The UNIQUE fields are "user" and "modality"

This code does not works, if old_points is > new_points this query updates only the points field. I have to update this 3 fields with the respective values when the new points are > than old.

How to fix ?

You are not comparing your $score variable to your points field. — jeroen
– jeroen, Commented Mar 5, 2014 at 14:33

Firman Hidayat · Accepted Answer · 2014-03-05 14:46:33Z

1

use this

points = IF(VALUES(points) > points, VALUES(points), points),
time = IF(VALUES(points) >= points, VALUES(time), time),
level = IF(VALUES(points) >= points, VALUES(level), level)

notice the >= symbols

explanation: in first line the value of points is already changed

answered Mar 5, 2014 at 14:46

Firman Hidayat

4355 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Firman Hidayat Over a year ago

is it working? then pick me as the accepted answer :)

fancyPants · Accepted Answer · 2014-03-05 14:53:02Z

Following is just a proof, that your code is fine and is totally doing what you expect.

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.1.41-3ubuntu12.7-log |
+------------------------+
1 row in set (0.00 sec)

 create table highscore(user int, points int, modality int, time int, level int, unique key idx_u_m (use                         r, modality));
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO highscore(user, points, modality, time, level)
    -> VALUES(1, 1, 1, 1, 1)
    -> ON DUPLICATE KEY UPDATE
    -> points = IF(VALUES(points) > points, VALUES(points), points),
    -> time = IF(VALUES(points) > points, VALUES(time), time),
    -> level = IF(VALUES(points) > points, VALUES(level), level);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO highscore(user, points, modality, time, level)
    -> VALUES(1, 1, 1, 1, 1)
    -> ON DUPLICATE KEY UPDATE
    -> points = IF(VALUES(points) > points, VALUES(points), points),
    -> time = IF(VALUES(points) > points, VALUES(time), time),
    -> level = IF(VALUES(points) > points, VALUES(level), level);
Query OK, 0 rows affected (0.15 sec)

mysql> select * from highscore;
+------+--------+----------+------+-------+
| user | points | modality | time | level |
+------+--------+----------+------+-------+
|    1 |      1 |        1 |    1 |     1 |
+------+--------+----------+------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO highscore(user, points, modality, time, level)
    -> VALUES(1, 2, 1, 1, 1)
    -> ON DUPLICATE KEY UPDATE
    -> points = IF(VALUES(points) > points, VALUES(points), points),
    -> time = IF(VALUES(points) > points, VALUES(time), time),
    -> level = IF(VALUES(points) > points, VALUES(level), level);
Query OK, 2 rows affected (0.09 sec)

mysql> select * from highscore;                                                                                                        +------+--------+----------+------+-------+
| user | points | modality | time | level |
+------+--------+----------+------+-------+
|    1 |      2 |        1 |    1 |     1 |
+------+--------+----------+------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO highscore(user, points, modality, time, level)
    -> VALUES(2, 2, 1, 1, 1)
    -> ON DUPLICATE KEY UPDATE
    -> points = IF(VALUES(points) > points, VALUES(points), points),
    -> time = IF(VALUES(points) > points, VALUES(time), time),
    -> level = IF(VALUES(points) > points, VALUES(level), level);
Query OK, 1 row affected (0.03 sec)

mysql> select * from highscore;
+------+--------+----------+------+-------+
| user | points | modality | time | level |
+------+--------+----------+------+-------+
|    1 |      2 |        1 |    1 |     1 |
|    2 |      2 |        1 |    1 |     1 |
+------+--------+----------+------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO highscore(user, points, modality, time, level)
    -> VALUES(2, 3, 1, 1, 1)
    -> ON DUPLICATE KEY UPDATE
    -> points = IF(VALUES(points) > points, VALUES(points), points),
    -> time = IF(VALUES(points) > points, VALUES(time), time),
    -> level = IF(VALUES(points) > points, VALUES(level), level);
Query OK, 2 rows affected (0.08 sec)

mysql> select * from highscore;
+------+--------+----------+------+-------+
| user | points | modality | time | level |
+------+--------+----------+------+-------+
|    1 |      2 |        1 |    1 |     1 |
|    2 |      3 |        1 |    1 |     1 |
+------+--------+----------+------+-------+
2 rows in set (0.00 sec)

Collectives™ on Stack Overflow

on duplicate key update if with multiple fields

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related