Mysql – INSERT… ON DUPLICATE KEY UPDATE not working as I expect

duplicationinsertMySQLupdate

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:

enter image description here

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

enter image description here

What is wrong with my statement?

Best Answer

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.

Related Question