Sling Academy
Home/MySQL/MySQL: ON DUPLICATE KEY UPDATE Statement – Explained with Examples

MySQL: ON DUPLICATE KEY UPDATE Statement – Explained with Examples

Last updated: January 27, 2024

Introduction

When working with databases, ensuring data integrity often involves handling situations where insert operations may lead to duplicate records. MySQL provides an elegant solution to this common problem with the ON DUPLICATE KEY UPDATE statement. This article will explore the usage of this statement with a progression of examples from the basic to the more advanced cases.

Understanding Keys and Unique Constraints

Before delving into ON DUPLICATE KEY UPDATE, understanding the importance of unique keys in a MySQL table is crucial. In database terms, a unique key is a constraint that ensures the data contained in certain columns is unique across all the rows present in a table.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example, the username and email columns have unique constraints, preventing duplicates of these values in the table.

Basic Usage of ON DUPLICATE KEY UPDATE

The most basic form of the ON DUPLICATE KEY UPDATE clause is seen in a simple INSERT statement, which updates a record if it already exists or inserts a new record if it does not.

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')
  ON DUPLICATE KEY UPDATE email = '[email protected]';

This query will add a new user named ‘john_doe’ with the specified email to the users table. If ‘john_doe’ already exists in the table, only the email will be updated.

Advanced Usage with Conditionals and Multiple Columns

Next, let’s look at an advanced example where conditionals and multiple columns come into play:

INSERT INTO users (username, email, created_at) VALUES ('jane_doe', '[email protected]', NOW())
  ON DUPLICATE KEY UPDATE
    email = IF(VALUES(email) != email, VALUES(email), email),
    created_at = IF(VALUES(created_at) > created_at, VALUES(created_at), created_at);

This example updates both the email and created_at columns if a record for ‘jane_doe’ exists and sets conditions for when to update each field.

Handling Complex Expressions

ON DUPLICATE KEY UPDATE also supports the usage of more complex expressions and functions. The following example demonstrates such usage:

INSERT INTO users (username, email, score) VALUES('joe_doe', '[email protected]', 10)
  ON DUPLICATE KEY UPDATE
    score = score + VALUES(score),
    email = '[email protected]';

Here, if ‘joe_doe’ already exists, his score is incremented by 10, and his email is updated.

Using INSERT … ON DUPLICATE KEY UPDATE with Multiple Rows

You can also apply ON DUPLICATE KEY UPDATE to multiple-row inserts, as shown:

INSERT INTO users (username, email) VALUES
  ('alice', '[email protected]'),
  ('bob', '[email protected]')
ON DUPLICATE KEY UPDATE
  email = VALUES(email);

If ‘alice’ or ‘bob’ exists in the database, their emails will be updated; otherwise, new records will be added.

ON DUPLICATE KEY UPDATE and AUTO_INCREMENT columns

The ON DUPLICATE KEY UPDATE clause works seamlessly with AUTO_INCREMENT columns:

INSERT INTO users (username, email) VALUES ('charlie', '[email protected]')
  ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    email = VALUES(email);

If the ‘charlie’ row exists, the email is updated and the LAST_INSERT_ID() function ensures that the AUTO_INCREMENT value remains consistent.

Practical Considerations

When using ON DUPLICATE KEY UPDATE, consider the performance implications and ensure that you have proper indexes in place to make the most of its efficiency. Also, be aware of potential race conditions when working with concurrent database access.

Conclusion

The ON DUPLICATE KEY UPDATE clause in MySQL is a powerful tool for managing insert operations that would otherwise result in duplication. By leveraging the techniques discussed in this article, you can maintain data integrity and avoid redundancy with ease. As with any database operation, understanding the features—and their potential pitfalls—is key to using them effectively.

Next Article: MySQL 8: INSERT IGNORE statement – Explained with examples

Previous Article: MySQL Upsert: Update if exists, insert if not

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples