MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields

by

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.

More Related Contents:

  1. What are differences between INSERT and UPDATE in MySQL?
  2. How to create and insert a JSON object using MySQL queries?
  3. Insert into a MySQL table or update if exists
  4. Inserting multiple rows in mysql
  5. MySQL – UPDATE query based on SELECT Query
  6. MySQL update table based on another tables value
  7. UPDATE multiple rows with different values in one query in MySQL
  8. Multiple Updates in MySQL
  9. mysql update column with value from another table
  10. Column count doesn’t match value count at row 1
  11. Insert into multiple tables in one query
  12. UPDATE multiple tables in MySQL using LEFT JOIN
  13. MySQL – UPDATE query with LIMIT
  14. mysql after insert trigger which updates another table’s column
  15. update multiple rows using limit in mysql?
  16. PHP MYSQL UPDATE if Exist or INSERT if not?
  17. MySQL update a joined table
  18. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  19. MySQL/SQL: Update with correlated subquery from the updated table itself
  20. MySQL: update a field only if condition is met
  21. Advanced MySql Query: Update table with info from another table
  22. MySql – Way to update portion of a string?
  23. MySQL Error: Incorrect usage of UPDATE and LIMIT
  24. Does MySQL overwrite a column of same value on update?
  25. MySQL UPDATE syntax with multiple tables using WHERE clause
  26. mySQL UPDATE query returns “0 rows affected”
  27. MySQL UPDATE and SELECT in one pass
  28. Update multiple rows with multiple ‘where’ clauses for each individual row
  29. MySQL update statement to store ranking positions
  30. INSERT INTO fails with node-mysql

Leave a Comment