MySQL - on duplicate key not updating table

Question

I have a table defined as:

----------------------------------------
|  a (VARCHAR)  | b (DATE) |  c (INT)  |
----------------------------------------

With an INDEX defined as:

CREATE INDEX table_index ON table (a,b);

When running the following code:

INSERT INTO test_table
        (a,b,c)
    VALUES
        ('test', '2017-10-06 08:00:00', 1)
    ON DUPLICATE KEY UPDATE
        c = c + VALUES(c);

A new row is created each time the above query is called where I want an update operation to be done on the table instead. i.e if columns a and b are the same I want c to be updated, else a new row be created.

Why isn't the ON DUPLICATE KEY working?

Answer 1

Your query will not insert new record every time if you mark column c as primary key or unique key.

DUPLICATE KEY finds duplicate primary or unique key, if it is then it'll update the same record else create the new one.

Answer 2

Your query is working perfectly fine here with me.