ON DUPLICATE KEY UPDATE with a multi field index

Question

I have this test table with one row entry and 2 indexes, the first a primary key and then a unique index for a and b columns:

CREATE TABLE IF NOT EXISTS `test` (
  `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 `a` (`a`,`b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `a`, `b`, `c`) VALUES
(1, 1, 2, 3);

Now I am trying to do the following

INSERT INTO test
   (a, b, c)
VALUES (1, 2, 100)
ON DUPLICATE KEY UPDATE c = c

AND I was expecting to have the value for column c updated form 3 to 100. But this doesnt happend and I get no errors. What am I doing wrong?

Answer 1

You need to use ON DUPLICATE KEY UPDATE c = VALUES(c) instead.