Adding to Integer in MySQL UPDATE, INSERT if Non-Existent

Question

Similar to this question, I want to INSERT a row into a MySQL table if the row, identified by the value of only one column, doesn't exist, and UPDATE it if it does. However, I want to simply add one to an integer column of the row instead of changing the column value entirely.

The table is as follows:

ref VARCHAR(16), num INT

If "value" exists as ref in the table, I want to:

UPDATE table SET num = num + 1 WHERE ref = "value";

But if it doesn't exist, I want to:

INSERT INTO table SET ref = "value", num = 1;

I know that there are a large number of questions about inserting if non-existent, update if existent, many with a good answer, but none seem to answer this problem directly. I'm sure it's simple, but I worry that the ON DUPLICATE KEY would require both columns to be identical. In my case, it needs to work, no matter what number num is.

ALTER TABLE `table` ADD UNIQUE `unique_key` ( `ref` ) your unique key is ref column not num — ah_hau, Aug 04 '16 at 05:42
If you have a unique key that causes a duplicate clash, the IODKU will work. You need a key clash. You may have many that could clash at once. All you need is at least one to. And you will note that you are not showing your schema with `show create table myTable` for anyone to help in that regard (unless that `ref VARCHAR(16), num INT` is it) — Drew, Aug 04 '16 at 06:01

score 0 · Answer 1 · answered Aug 04 '16 at 08:29

0

In MySQL you can use INSERT OR UPDATE syntax. If a uniqueness (index) is violated by the INSERT, it runs the update.

answered Aug 04 '16 at 08:29

FDavidov

3,505
6
23
59

score 0 · Answer 2 · answered Aug 05 '16 at 14:36

Assuming you have a unique key on the ref field then you can as you suggested use ON DUPLICATE KEY UPDATE syntax.

The field you are updating doesn't have to be updated with the value. You can set the default value in the inset and update the column = column + 1:=

INSERT INTO table(ref, num)
VALUES("somevalue", 1)
ON DUPLICATE KEY UPDATE num=num + 1

If the num field had a default value of 1 then you wouldn't even need it in the main part of the INSERT statement:-

INSERT INTO table(ref)
VALUES("somevalue")
ON DUPLICATE KEY UPDATE num=num + 1

Note that there are possible issues with duplicate key processing if you have multiple unique indexes (ie, possibly an auto increment primary key. One notable one is that if you land up with a record where the auto increment primary key has a value of 0 then future INSERT ... ON DUPLICATE KEY UPDATE statements can just update this record instead. Ie, you insert a record either not specifying a primary key, or specifying one of NULL, and MySQL regards that as a duplicate of the primary key of 0, hence tries to update.

Adding to Integer in MySQL UPDATE, INSERT if Non-Existent

2 Answers2