INSERT... ON DUPLICATE KEY UPDATE not working as I expect I have a table called "Example"

CREATE TABLE IF NOT EXISTS `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


I want to insert values if not exists and if the value exists then update, so I am using following statement:

INSERT INTO example (a, b, c) VALUES (1,2,3) 
  ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);


After the above queries executed the table look like this:



Again I execute the above statement, the result looks like this: 



What is wrong with my statement?
Well this is the insert bit that you are using:


  INSERT INTO example (a, b, c) VALUES (1,2,3) ....


here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c.

The row in this case is updated when you provide a primary key (that you want to check is duplicate or not), along with the rest of the row data.
If you need to check and update according to record id, you have to provide the KEY as well, which in your case is id.

Try something like this:

INSERT INTO example (id, a, b, c) VALUES (1,1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);


Now if, the id is duplicate, the row will update.