MySQL UPSERT: Insert or Update on Duplicate Key

MySQL provides the INSERT ... ON DUPLICATE KEY UPDATE statement to perform upsert operations, which inserts a new row if the unique key doesn’t exist, or updates the existing row if the unique key is already present. This is the standard solution for handling duplicate key constraints in MySQL without errors.

Contents

• Basic Syntax Explanation
• Modern Syntax with Row Aliases
• Practical Examples
• How It Works Internally
• Performance Considerations
• Alternative Approaches

Basic Syntax Explanation

The INSERT ... ON DUPLICATE KEY UPDATE clause is an extension to the standard INSERT statement in MySQL. When you attempt to insert a row and a duplicate key violation occurs (for a PRIMARY or UNIQUE key), MySQL instead performs an UPDATE on the existing row.

The basic syntax structure is:

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

The VALUES(column_name) function refers to the value that was originally specified for that column in the INSERT statement. This ensures you’re updating with the intended new values.

According to the official MySQL documentation, “INSERT … ON DUPLICATE KEY UPDATE is a MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.”

Modern Syntax with Row Aliases

Beginning with MySQL 8.0.19, you can use row aliases to access new column values in a cleaner, more readable way. This modern approach is highly recommended for new applications.

The syntax allows you to specify an alias for the row being inserted, making it easier to reference the new values in the UPDATE clause:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
AS new_row ON DUPLICATE KEY UPDATE
    column1 = new_row.column1,
    column2 = new_row.column2,
    column3 = new_row.column3;

You can also specify column aliases:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
AS new_row(col1, col2, col3) ON DUPLICATE KEY UPDATE
    column1 = col1,
    column2 = col2,
    column3 = col3;

As shown in the MySQL documentation examples, this approach allows for more complex operations:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;

The row alias must not be the same as the name of the table, and if column aliases are not used or are the same as the column names, the values can still be accessed directly.

Practical Examples

Let’s apply this to your specific example. Assuming you have a table like this:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Basic Example

To insert a new user or update an existing one by ID:

INSERT INTO users (ID, NAME, AGE)
VALUES(1, "Alice", 25)
ON DUPLICATE KEY UPDATE 
    NAME = VALUES(NAME),
    AGE = VALUES(AGE);

Modern Syntax Example

Using row aliases for better readability:

INSERT INTO users (ID, NAME, AGE)
VALUES(1, "Alice", 25)
AS user_data ON DUPLICATE KEY UPDATE
    NAME = user_data.NAME,
    AGE = user_data.age;

Multiple Rows Example

The statement also works with multiple row inserts:

INSERT INTO users (ID, NAME, AGE)
VALUES
    (1, "Alice", 25),
    (2, "Bob", 30),
    (3, "Charlie", 35)
AS user_data ON DUPLICATE KEY UPDATE
    NAME = user_data.NAME,
    AGE = user_data.age;

Conditional Updates

You can add conditions to your updates:

INSERT INTO users (ID, NAME, AGE)
VALUES(1, "Alice", 25)
AS user_data ON DUPLICATE KEY UPDATE
    NAME = IF(user_data.NAME != "", user_data.NAME, NAME),
    AGE = user_data.age;

This example shows how to update only if the new name is not empty, otherwise keep the existing name.

How It Works Internally

When you execute an INSERT ... ON DUPLICATE KEY UPDATE statement, MySQL follows this process:

1. Attempt Insert: MySQL first tries to insert the new row into the table.

2. Check for Duplicates: If the insert fails due to a duplicate key constraint on PRIMARY or UNIQUE keys:

3. Instead of Error: MySQL doesn’t return an error. Instead, it:

   • Identifies the existing row with the duplicate key
   • Executes the UPDATE statement specified in the ON DUPLICATE KEY UPDATE clause
   • Returns the number of affected rows (1 for update, 0 if no update occurred)

4. Return Information: The statement returns:

   • Number of rows affected (1 for insert/update, 0 if no changes)
   • For MySQL 8.0+: Information about whether it was an insert or update

Important Note: As noted in the MySQL 5.7 documentation, “For an InnoDB table where a is an auto-increment column, the INSERT statement increases the auto-increment value but the UPDATE does not.” This can have implications for your application logic.

Performance Considerations

When using INSERT ... ON DUPLICATE KEY UPDATE, consider these performance aspects:

Lock Behavior

• Row-level locking: InnoDB uses row-level locks, which is generally efficient
• Lock duration: The lock is held for the duration of both the insert attempt and potential update
• Deadlocks: Be aware of potential deadlocks in concurrent environments

Index Usage

• The unique key constraint must be properly indexed for optimal performance
• MySQL will use the same index it would for duplicate key checking
• Ensure your primary/unique keys have appropriate indexes

Bulk Operations

• The statement works efficiently with multiple row inserts
• Each row is processed individually for duplicate key checking
• Consider batching operations for better throughput

Transaction Impact

• The entire operation (attempted insert + potential update) occurs within a single transaction
• This provides atomicity guarantees for your upsert operation

According to Prisma’s Data Guide, this approach is “typically referred to as an ‘upsert’ operation” and is a standard pattern in database applications.

Alternative Approaches

While INSERT ... ON DUPLICATE KEY UPDATE is the standard MySQL approach, there are alternatives worth considering:

REPLACE Statement

The REPLACE statement first deletes the existing row and then inserts the new one:

REPLACE INTO users (ID, NAME, AGE) VALUES(1, "Alice", 25);

Pros: Simple syntax
Cons:

• Deletes and recreates the row (triggers fire again)
• Auto-increment values are affected
• Less efficient than ON DUPLICATE KEY UPDATE

Stored Procedures

You can create stored procedures to handle the logic:

CREATE PROCEDURE upsert_user(IN p_id INT, IN p_name VARCHAR(50), IN p_age INT)
BEGIN
    INSERT INTO users (ID, NAME, AGE) VALUES(p_id, p_name, p_age)
    ON DUPLICATE KEY UPDATE 
        NAME = p_name,
        AGE = p_age;
END;

Application Logic

In your application code:

START TRANSACTION;
SELECT COUNT(*) FROM users WHERE ID = 1 FOR UPDATE;
-- If count = 0, INSERT
-- If count > 0, UPDATE
COMMIT;

Pros: Full control over the logic
Cons: More complex, requires transaction management

Comparison with INSERT IGNORE

As you mentioned, INSERT IGNORE prevents errors but doesn’t update existing rows. It simply skips duplicate rows entirely.

INSERT IGNORE INTO users (ID, NAME, AGE) VALUES(1, "Alice", 25);

This is useful when you only want to insert new records and skip duplicates, but not when you need to update existing records.

Sources

1. MySQL 8.4 Reference Manual - INSERT … ON DUPLICATE KEY UPDATE
2. MySQL 8.0 Reference Manual - INSERT … ON DUPLICATE KEY UPDATE
3. MySQL 5.7 Reference Manual - INSERT … ON DUPLICATE KEY UPDATE
4. Prisma Data Guide - ON DUPLICATE KEY UPDATE
5. MySQL INSERT ON DUPLICATE KEY UPDATE Statement
6. Understanding MySQL INSERT ON DUPLICATE UPDATE OR UPSERT
7. MySQL UPSERT Explained with Examples

Conclusion

The INSERT ... ON DUPLICATE KEY UPDATE statement is MySQL’s built-in solution for upsert operations, providing a clean and efficient way to either insert new rows or update existing ones based on unique key constraints.

For your specific use case, the recommended syntax is:

INSERT INTO table_name (ID, NAME, AGE)
VALUES(1, "Alice", 25)
AS new_data ON DUPLICATE KEY UPDATE
    NAME = new_data.NAME,
    AGE = new_data.age;

This approach offers several advantages:

• Atomicity: The operation is performed in a single statement
• Performance: Efficient with proper indexing
• Readability: Modern syntax with row aliases makes code clearer
• Flexibility: Supports complex update conditions and bulk operations

When implementing upsert operations in your applications, consider:

• Using row aliases (MySQL 8.0.19+) for better code maintainability
• Proper indexing on your unique key columns
• Transaction isolation levels for concurrent access
• Alternative approaches like REPLACE or stored procedures for specific use cases

This pattern is fundamental to many database applications and is worth mastering for efficient data management in MySQL.