Search code examples
mysqlprimary-keyauto-increment

mySQL column that is auto incremented every for every duplicate?

Let's say I have a table of objects with the following characteristics :

- name - count

Name is the primary key.

Sample data : 

Shoes - 1
Pants - 1

Is it possible to increment an object's count every time an object with the same primary key is added to the table?

For example if I were to add an object with the name of Shoe, the table would show:

 Shoes - 2
 Pants - 1
Solution

  • You could use INSERT ... ON DUPLICATE KEY UPDATE, which performs an update instead of insert if the name you are attempting to insert already exists :

    INSERT INTO tablename (name,count) VALUES ('Shoes',1)
  ON DUPLICATE KEY UPDATE count=count+1;