By kiriloff


2013-05-04 19:06:12 8 Comments

From MySQL 4.1.0 onwards, it is possible to add ON DUPLICATE KEY UPDATE statement to specify behavior when values inserted (with INSERT or SET or VALUES) are already in destination table w.r.t. PRIMARY KEY or some UNIQUE field. If value for PRIMARY KEY or some UNIQUE field are already in table, INSERT is replaced by an UPDATE.

  • How does ON DUPLICATE KEY UPDATE behave in case there are multiple UNIQUE fields in my table ?

  • Can I have one update only, if either UNIQUE field is matched ?

  • Can I have an update only if both UNIQUE fields are matched simultaneously ?

2 comments

@newtover 2013-05-04 22:34:16

  1. how does MySQL behave ... It behaves as expected, that is executes ON DUPLICATE KEY clause.

  2. Can I have one update for either... In reality, you have only one ON DUPLICATE KEY clause, so you need to put some code to differentiate which constraint was involved. Fortunatelly, it is possible. The only thing you should know, the order of assignment matters, and you can assign multiple times. Suppose, you have unique constraint on a and b, and you want to update c only if a uniqueness is involved: ... KEY UPDATE c = IF(a = VALUES(a) and b <> VALUES(b), VALUES(c), c), b = VALUES(b)

    but if you change the order of assignments the second condition within if will be always false.

  3. See 2.

@kiriloff 2013-05-04 19:06:12

Consider

INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

if a and b are UNIQUE fields, UPDATE occurs on a = 1 OR b = 2. Also when condition a = 1 OR b = 2 is met by two or more entries, update is done only once.

Ex here table table with Id and Name UNIQUE fields

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6

If query is

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)

then we get

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6
1      C        7

which violates uniqueness of Id and Name. Now with

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)
ON DUPLICATE KEY UPDATE Value = 7

we get

Id     Name     Value 
1      P        7 
2      C        7 
3      D        29 
4      A        6

Behavior on multiple keys is the following

UPDATE in ON DUPLICATE KEY UPDATE is performed if one of the UNIQUE field equals the value to be inserted. Here, UPDATE is performed on Id = 1 OR Name = C. It is equivalent to

UPDATE table 
SET Value = 7
WHERE Id = 1 OR Name = C

What if I want one update only, for either key

Can use UPDATE statement with LIMIT keyword

UPDATE table 
SET Value = 7
WHERE Id = 1 OR Name = C
LIMIT 1;

which will give

Id     Name     Value 
1      P        7 
2      C        3 
3      D        29 
4      A        6

What if I want one update only if values for both keys are matched

One solution is to ALTER TABLE and make the PRIMARY KEY (or uniqueness) work on both fields.

ALTER TABLE table 
DROP PRIMARY KEY
ADD PRIMARY KEY (Id, Name);

Now, on

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)
ON DUPLICATE KEY UPDATE Value = 7

we get

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6
1      C        7

since no duplicate (on both keys) is found.

@jloria 2014-10-31 06:27:28

If you have a table with two columns as primary key, and want work with the ON DUPLICATE KEY, must avoid add unique index to the pk columns, because this will update if pk1 or pk2 are present in the values.

@CMCDragonkai 2015-07-25 12:24:54

Is there any way of having a unique constraint on the 2nd column, but have it so it does not affect this duplicate key update operation? This means the insert or update fails if the unique constraint is not met.

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

  • 2009-02-14 05:24:06
  • Thomas G Henry
  • 824256 View
  • 819 Score
  • 11 Answer
  • Tags:   mysql insert

6 Answered Questions

[SOLVED] How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

17 Answered Questions

[SOLVED] MySQL error code: 1175 during UPDATE in MySQL Workbench

11 Answered Questions

[SOLVED] Insert into a MySQL table or update if exists

16 Answered Questions

[SOLVED] Insert, on duplicate update in PostgreSQL?

24 Answered Questions

[SOLVED] Finding duplicate values in MySQL

  • 2009-03-27 04:22:12
  • Jon Tackabury
  • 595976 View
  • 739 Score
  • 24 Answer
  • Tags:   mysql

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

17 Answered Questions

[SOLVED] SQLite - UPSERT *not* INSERT or REPLACE

  • 2009-01-07 01:47:23
  • Mike Trader
  • 278448 View
  • 509 Score
  • 17 Answer
  • Tags:   sql sqlite upsert

8 Answered Questions

[SOLVED] On Duplicate Key Update same as insert

12 Answered Questions

[SOLVED] Oracle: how to UPSERT (update or insert into a table?)

  • 2008-10-26 01:24:06
  • Mark Harrison
  • 394906 View
  • 278 Score
  • 12 Answer
  • Tags:   sql oracle merge upsert

Sponsored Content