6

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?

  • 2
    How do you define "duplicates"? All three (a,b,c) being the same? Just (a)? Just (b)? Two of them (a,c)? Or (b,c)? Or (a,c)? – ypercubeᵀᴹ Aug 12 '15 at 11:04
  • I don't know exactly what do you mean ? I am very new to this query and I am expecting functionality like this video tutorial youtube.com/watch?v=jovkZi42J1U – Relax Aug 12 '15 at 11:07
  • 1
    I mean: what is the problem with the 2 rows that have been inserted? Why shouldn't they be inserted? Why don't you want them both? – ypercubeᵀᴹ Aug 12 '15 at 11:08
  • 1
    Let us continue this discussion in chat. – Relax Aug 12 '15 at 11:15
  • 1
    Your table definition says 'on duplicate key UPDATE', however your key is auto-incrementing and so, unless you explicitly try to insert into the ID field, the key will never be a duplicate. (1,2,3) is not the key, and is therefore not duplicate – Mark Sinkinson Aug 12 '15 at 12:35
7

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.

  • What did you mean here "would keep the values for (a,b,c) exactly the same. Nothing would change." Please explain with example, Both statements are working – Relax Aug 12 '15 at 17:14
  • Once you create the unique index on (a,b,c) , doing INSERT INTO example (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c); would update 1 with 1, update 2 with 2, and update 3 with 3. In effect, nothing changed. Doing INSERT IGNORE INTO example (a, b, c) VALUES (1,2,3); would determine ahead of time that nothing needs to change. – RolandoMySQLDBA Aug 12 '15 at 17:30
  • @RolandoMySQLDBA If I do: INSERT INTO example(a,b,c) VALUES(1,2,3) ON DUPLICATE KEY UPDATE id=id. then still it would be same as you told, right? I mean in this case also, row will be same as it is. Please clarify. – hellodear Jan 5 '16 at 14:54
  • 2
    Instead of INSERT IGNORE INTO example (a, b, c) VALUES (1,2,3);, you should use INSERT INTO example (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=id; -- the latter will also no-op, but it will not ignore other, unrelated errors. – Matthew Read Jan 29 '17 at 6:31
7

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 Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.