CODEWITHSUNDEEP
mysqlincrementdecrementon-duplicate-key
nickdnk
nickdnk

Reputation: 4300

ON DUPLICATE KEY UPDATE - decrement value in MySQL

The following seems odds to me:

INSERT INTO sometable (UNIQUEVALUE,NUMERICVALUE) VALUES ('valuethatexists','100') ON DUPLICATE KEY UPDATE NUMERICVALUE = NUMERICVALUE+VALUES(NUMERICVALUE);

Assume your NUMERICVALUE is at 0.

The above would change it to 100 - which does work.

If, however, you then input -100, it does not work properly.

INSERT INTO sometable (UNIQUEVALUE,NUMERICVALUE) VALUES ('valuethatexists','-100') ON DUPLICATE KEY UPDATE NUMERICVALUE = NUMERICVALUE+VALUES(NUMERICVALUE);

The above statement should return it to 0. It does not, in my case. It remains at 100.

Am I missing something?

Edit: This goes wrong somewhere else. I am doing this with PHP. The actual code exhibiting this bug looks like this:

Edit 2: This had nothing to do with PHP either. The problem was the NUMERIC value was UNSIGNED in my production environment, meaning VALUES(NUMERICVALUE) was brought from -100 to 0 before it was used.

Upvotes: 1

Views: 470

Answers (2)

nickdnk
nickdnk

Reputation: 4300

While Benjamin's answer is correct, the root of the issue turned out to be the fact that the NUMERICVALUE column was UNSIGNED, so whenever I input -100, it was turned into 0 before it was evaluated as VALUES(NUMERICVALUE). If this is to be considered a bug or not I don't know.

Obviously the result of the final evaluation should not be negative, but I don't know how clever it is to silently turn it into 0. I had logic in place making sure the value in question would never be below 0 anyway by never passing a negative value larger than what was already in the row.

Upvotes: 0

BenMorel
BenMorel

Reputation: 36484

On my MySQL server (5.7.12), it does work as expected:

mysql> CREATE TABLE sometable (
       UNIQUEVALUE VARCHAR(16) NOT NULL PRIMARY KEY, 
       NUMERICVALUE INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO sometable (UNIQUEVALUE,NUMERICVALUE)
       VALUES ('valuethatexists','100')
       ON DUPLICATE KEY UPDATE NUMERICVALUE = NUMERICVALUE+VALUES(NUMERICVALUE);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM sometable;
+-----------------+--------------+
| UNIQUEVALUE     | NUMERICVALUE |
+-----------------+--------------+
| valuethatexists |          100 |
+-----------------+--------------+
1 row in set (0.00 sec)

mysql> INSERT INTO sometable (UNIQUEVALUE,NUMERICVALUE)
       VALUES ('valuethatexists','-100')
       ON DUPLICATE KEY UPDATE NUMERICVALUE = NUMERICVALUE+VALUES(NUMERICVALUE);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM sometable;
+-----------------+--------------+
| UNIQUEVALUE     | NUMERICVALUE |
+-----------------+--------------+
| valuethatexists |            0 |
+-----------------+--------------+
1 row in set (0.00 sec)

Which version of MySQL are you using? Can you execute the exact statements above and see if you have different results?

Upvotes: 1

Related Questions