INSERT... ON DUPLICATE KEY UPDATE not working as I expect

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.

Your Original Query

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

If you consider (a,b,c) a unique key, there are two things you need to do

First, add a unique index

ALTER TABLE example ADD UNIQUE KEY abc_ndx (a,b,c);

so the table structure would become

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`),
  UNIQUE KEY abc_ndx (a,b,c)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Second, you need to change the query completely. Why ?

If (a,b,c) is unique, the, running

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

would keep the values for (a,b,c) exactly the same. Nothing would change.

Therefore, I recommend changing the query to the following

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

The query is simpler, and it has the same end result.