Practical MySQL & MariaDB

MySQL UPSERT Explained: INSERT … ON DUPLICATE KEY UPDATE Guide with Examples

SQL Basics
目次

1. What Is UPSERT?

Overview

“UPSERT” refers to a database feature that combines “INSERT” and “UPDATE” operations. In other words, if the data does not already exist, it is inserted; if the same data already exists, it is updated. By using this feature, you can perform efficient operations while maintaining data consistency.

In MySQL, this functionality is implemented using the INSERT ... ON DUPLICATE KEY UPDATE syntax. This feature allows you to avoid duplicate key errors and update existing records even when duplicate keys occur.

Use Cases

  • Customer Management Systems: Add new customer data if it does not exist, and update existing customer information when it changes.
  • Product Inventory Management: Add new products while updating the stock quantity of existing products.

Advantages 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 performed using the INSERT ... ON DUPLICATE KEY UPDATE syntax. With this syntax, if a duplicate key occurs, you can update part or all of the existing data instead of inserting new data.

Basic Syntax

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

Explanation:

  1. Use INSERT INTO to insert data.
  2. If the data being inserted already exists in the table, the ON DUPLICATE KEY UPDATE clause is executed, and the existing data is updated.

Example:

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

In the above example, if a user with user_id of 1 already exists, the name field is updated to ‘Taro Tanaka’. If the user does not exist, a new record is inserted.

3. Detailed SQL Syntax and Examples of UPSERT

Updating Multiple Columns

When using UPSERT, there are cases where you may want to update only specific columns. In such situations, you can specify only the necessary 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);

In this example, if a product with product_id 100 already exists, only the price column is updated, while other columns (such as name) remain unchanged.

4. Differences from Other Databases

Databases other than MySQL also provide similar functionality. For example, PostgreSQL and SQLite use INSERT ... ON CONFLICT or MERGE statements to achieve UPSERT-like behavior.

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 behavior when a duplicate key error occurs. In contrast, MySQL uses the ON DUPLICATE KEY UPDATE clause.

MySQL-Specific Characteristics

  • MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, and because the syntax differs from other databases, special care is required when migrating between systems.

5. Advanced UPSERT Techniques

Bulk UPSERT (Batch Processing of Multiple Records)

UPSERT can be executed not only for a single record but also for multiple records at once. This significantly improves the efficiency of database operations.

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

In this example, multiple product records are inserted at once. If duplicate keys exist, only the price field of the corresponding records is updated.

Using Stored Procedures for UPSERT

To optimize UPSERT processing, you can also use stored procedures. This allows you to create reusable logic inside the database, improving both readability and maintainability of your code.

6. Common Pitfalls and Important Considerations

Transactions and Deadlocks

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

Avoiding Gap Locks

  • You can reduce the likelihood of deadlocks by changing the transaction isolation level to READ COMMITTED.
  • If necessary, consider breaking a large UPSERT operation into smaller batches and executing multiple queries instead of one large statement.

7. Conclusion

The MySQL UPSERT feature is extremely useful for streamlining data insertion and updates while avoiding duplicate key errors. However, implementing UPSERT requires careful consideration of potential deadlocks and transaction settings. When used properly, it enables simpler and more efficient database operations.