MySQL UPSERT Guide: How to Use INSERT ON DUPLICATE KEY UPDATE with Examples

1. What is UPSERT?

Overview

“UPSERT” refers to a database operation that combines both INSERT and UPDATE. In other words, if the data does not already exist, it will be inserted, and if it does exist, it will be updated. This feature allows efficient operations while maintaining data consistency.

In MySQL, the INSERT ... ON DUPLICATE KEY UPDATE syntax provides this functionality. With it, you can avoid duplicate key errors and update existing records even when duplicate data situations occur.

Use Cases

• Customer Management Systems: Insert new customer data if it doesn’t exist, or update existing customer information when it changes.
• Inventory Management: Add new products while updating stock counts of existing ones.

Benefits of UPSERT in MySQL

• Avoids duplicate key errors
• Simplifies SQL queries
• Maintains data integrity

2. Basic Usage of UPSERT in MySQL

In MySQL, UPSERT operations are implemented using the INSERT ... ON DUPLICATE KEY UPDATE syntax. With this, if a duplicate key is found, the existing record is updated instead of inserting a new one.

Basic Syntax

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

Explanation:

1. INSERT INTO attempts to insert data into the table.
2. If the data already exists, the ON DUPLICATE KEY UPDATE clause executes and updates the existing record.

Example:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

In this example, if a user with user_id = 1 already exists, their name will be updated to ‘Taro Tanaka’. Otherwise, a new record will be inserted.

3. Detailed SQL Syntax and Examples for UPSERT

Updating Multiple Columns

When using UPSERT, you can choose to update only certain columns. In such cases, specify only the target columns in the ON DUPLICATE KEY UPDATE clause.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Here, if a product with product_id = 100 already exists, only the price column will be updated, leaving other columns such as name unchanged.

4. Differences with Other Databases

Other databases also provide UPSERT-like functionality. For example, PostgreSQL and SQLite use INSERT ... ON CONFLICT or MERGE as their equivalents.

PostgreSQL Example

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

In PostgreSQL and SQLite, the ON CONFLICT clause is used to control the behavior when duplicate key errors occur. In contrast, MySQL uses ON DUPLICATE KEY UPDATE.

MySQL’s Uniqueness

• MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, which differs from other databases. Special attention is needed during database migration.

5. Advanced UPSERT Usage

Bulk UPSERT (Multiple Records at Once)

UPSERT can also be applied to multiple records in one go, significantly improving efficiency in database operations.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Here, multiple products are inserted at once, and if a duplicate key exists, only the price is updated.

Using Stored Procedures for UPSERT

You can also use stored procedures to streamline UPSERT operations. This makes the code reusable, more readable, and easier to maintain.

6. Pitfalls and Considerations for UPSERT

Transactions and Deadlocks

When using UPSERT with large datasets, deadlocks may occur. If MySQL’s transaction isolation level is set to REPEATABLE READ, gap locks are more likely to happen.

Avoiding Gap Locks

• Changing the transaction isolation level to READ COMMITTED can reduce deadlock risks.
• Consider breaking UPSERT operations into smaller queries when necessary.

7. Conclusion

MySQL’s UPSERT functionality is a powerful tool for inserting and updating data efficiently while avoiding duplicate key errors. However, careful consideration of transaction settings and potential deadlocks is essential. Used correctly, UPSERT can make database operations simpler and more effective.