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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

1. In phpmyadmin go to the structure.

2. Down in inexes click on go.

3. index name set the same name like column for UNIQUE value. In this table chose column for UNIQUE value.

4. In php code, example:

   INSERT INTO table_name (id_second, column1, column2) VALUES (:id_second,:value1,:value2) ON DUPLICATE KEY UPDATE id_second = :id_second, value1 = :value1, value2 = :value2

5. The value that will not be repeated is set in the db in the indexes.

6. Behind the sentence: ON DUPLICATE KEY UPDATE, here you set values that can be adjusted.

7. Note that there is also a value that should not be repeated. It does not matter.