MySQL - Insert on Duplicate Key Update

MySQL: INSERT ON DUPLICATE KEY UPDATE

In modern application development, we often need to insert a new record into a database, but if a record with the same unique identifier already exists, we want to update it instead. This common operation is known as an “upsert” (a portmanteau of UPDATE and INSERT). MySQL provides a powerful and efficient way to handle this using the INSERT ... ON DUPLICATE KEY UPDATE statement.

This tutorial will guide you through the syntax, usage, and best practices for performing upsert operations in MySQL, both directly and through various programming languages.

The Core Concept: Handling Unique Key Constraints

When you attempt to insert a row into a table that would cause a duplicate value in a column with a PRIMARY KEY or UNIQUE index, MySQL normally throws an error (ERROR 1062: Duplicate entry). This is the database’s way of enforcing data integrity.

The ON DUPLICATE KEY UPDATE clause extends the INSERT statement, providing an alternative action. Instead of failing with an error, MySQL will perform the specified UPDATE on the existing row that caused the conflict.

Syntax

The basic syntax for the statement is as follows:

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

To reference the values you were trying to insert within the UPDATE clause, you can use the VALUES() function or, in MySQL 8.0.20 and later, a row alias for better readability.

-- Using VALUES()
ON DUPLICATE KEY UPDATE
  column_to_update = VALUES(column_to_update);

-- Using a row alias (MySQL 8.0.20+)
INSERT INTO table_name (c1, c2) VALUES (1, 2), (3, 4) AS new_values
ON DUPLICATE KEY UPDATE
  c2 = new_values.c2;

Example: Single-Row Upsert

Let’s start with a practical example. First, create a products table where each product has a unique SKU.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Now, let’s insert some initial products:

INSERT INTO products (sku, name, price) VALUES
('TSHIRT-RED-L', 'Red T-Shirt (Large)', 19.99),
('MUG-COFFEE-1', 'Standard Coffee Mug', 12.50);

If we try to insert a product with an existing SKU, we get an error:

INSERT INTO products (sku, name, price)
VALUES ('MUG-COFFEE-1', 'Branded Coffee Mug', 14.99);

-- Result:
-- ERROR 1062 (23000): Duplicate entry 'MUG-COFFEE-1' for key 'products.sku'

Now, let’s use ON DUPLICATE KEY UPDATE to update the price and name instead:

INSERT INTO products (sku, name, price)
VALUES ('MUG-COFFEE-1', 'Branded Coffee Mug', 14.99)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

Understanding the affected-rows Count

The number of affected rows returned by this query provides useful feedback:

• 1 row affected: A new row was inserted.
• 2 rows affected: An existing row was updated. This might seem counter-intuitive, but MySQL counts the deletion of the old row and the insertion of the new row as two operations.
• 0 rows affected: An existing row was matched, but the update resulted in no changes to its data.

Verification

Let’s check the table to see the updated record.

SELECT * FROM products WHERE sku = 'MUG-COFFEE-1';

id	sku	name	price	last_updated
2	MUG-COFFEE-1	Branded Coffee Mug	14.99	2023-10-27 10:30:00

Example: Multi-Row Upsert

The VALUES() function is particularly useful when inserting multiple rows at once, as it correctly references the values for the specific row that caused a duplicate key conflict.

INSERT INTO products (sku, name, price)
VALUES
    ('MUG-COFFEE-1', 'Branded Coffee Mug (New!)', 15.50), -- This one will update
    ('HAT-BLUE-OS', 'Blue Baseball Cap', 24.99)             -- This one will insert
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

After this query, the MUG-COFFEE-1 record is updated with the new name and price, and a new record for HAT-BLUE-OS is inserted. The query would report 3 rows affected (1 new record inserted + 1 existing record updated).

Implementing Upserts in Your Application

Using ON DUPLICATE KEY UPDATE from an application is straightforward. The key is to use prepared statements (or parameterized queries) to prevent SQL injection vulnerabilities. Below are modern, best-practice examples for popular languages.

Prerequisites for Application Code

Before running the examples, ensure you have the necessary database drivers installed:

• Node.js: npm install mysql2
• Python: pip install mysql-connector-python
• Java: Add the mysql-connector-java dependency to your pom.xml (Maven) or build.gradle (Gradle).
• PHP: The PDO_MySQL extension is typically enabled by default in modern PHP installations.

Node.js (with async/await and mysql2)

const mysql = require('mysql2/promise');

async function upsertProduct(product) {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'your_password',
            database: 'your_database'
        });

        const sql = `
            INSERT INTO products (sku, name, price)
            VALUES (?, ?, ?)
            ON DUPLICATE KEY UPDATE
                name = VALUES(name),
                price = VALUES(price);
        `;

        const [result] = await connection.execute(sql, [product.sku, product.name, product.price]);

        if (result.affectedRows === 1) {
            console.log(`Product ${product.sku} inserted with ID: ${result.insertId}`);
        } else if (result.affectedRows === 2) {
            console.log(`Product ${product.sku} was updated.`);
        } else {
            console.log(`Product ${product.sku} already existed with the same data.`);
        }

    } catch (error) {
        console.error('Database operation failed:', error);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// Example usage
upsertProduct({ sku: 'TSHIRT-RED-L', name: 'Red T-Shirt (Large)', price: 21.99 }); // Should update
upsertProduct({ sku: 'SOCKS-BLK-M', name: 'Black Socks (Medium)', price: 9.99 }); // Should insert

Python (with mysql-connector-python)

import mysql.connector
from mysql.connector import errorcode

def upsert_product(config, product):
    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(prepared=True) as cursor:
                sql = """
                    INSERT INTO products (sku, name, price)
                    VALUES (%s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        price = VALUES(price);
                """
                cursor.execute(sql, (product['sku'], product['name'], product['price']))
                connection.commit()
                print(f"Upsert operation successful for SKU: {product['sku']}. Rows affected: {cursor.rowcount}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")

# --- Usage ---
DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

product_to_update = {'sku': 'TSHIRT-RED-L', 'name': 'Red T-Shirt (Large)', 'price': 21.99}
product_to_insert = {'sku': 'SOCKS-BLK-M', 'name': 'Black Socks (Medium)', 'price': 9.99}

upsert_product(DB_CONFIG, product_to_update)
upsert_product(DB_CONFIG, product_to_insert)

Java (with JDBC and try-with-resources)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ProductUpsert {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public void upsertProduct(String sku, String name, double price) {
        String sql = "INSERT INTO products (sku, name, price) "
                   + "VALUES (?, ?, ?) "
                   + "ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price)";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, sku);
            pstmt.setString(2, name);
            pstmt.setDouble(3, price);

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                System.out.println("Upsert successful for SKU: " + sku);
            } else {
                System.out.println("No changes made for SKU: " + sku);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        ProductUpsert dao = new ProductUpsert();
        dao.upsertProduct("MUG-COFFEE-1", "Premium Branded Mug", 17.99); // Update
        dao.upsertProduct("KEYCHAIN-LOGO-1", "Company Logo Keychain", 5.99); // Insert
    }
}

PHP (with PDO)

<?php
function upsertProduct(string $sku, string $name, float $price) {
    $host = '127.0.0.1';
    $db   = 'your_database';
    $user = 'root';
    $pass = 'your_password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    $sql = "INSERT INTO products (sku, name, price)
            VALUES (:sku, :name, :price)
            ON DUPLICATE KEY UPDATE
                name = VALUES(name),
                price = VALUES(price);";

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['sku' => $sku, 'name' => $name, 'price' => $price]);

        echo "Upsert operation successful for SKU: $sku. Rows affected: " . $stmt->rowCount() . "\n";

    } catch (\PDOException $e) {
        throw new \PDOException($e->getMessage(), (int)$e->getCode());
    }
}

// Example usage
upsertProduct('MUG-COFFEE-1', 'Premium Branded Mug', 17.99); // Update
upsertProduct('KEYCHAIN-LOGO-1', 'Company Logo Keychain', 5.99); // Insert
?>

Common Pitfalls and Best Practices

• Missing Unique/Primary Key: ON DUPLICATE KEY UPDATE does nothing if there is no PRIMARY KEY or UNIQUE index on a column in your INSERT statement. The query will simply insert a new row every time.
• Performance: This statement is generally much faster than performing a SELECT followed by an INSERT or UPDATE, as it saves a round-trip to the database server. However, on tables with high write contention, it can still cause locking. Always benchmark in a relevant environment.
• Use Aliases: For MySQL 8.0.20+, prefer using row aliases (... AS new_values ON DUPLICATE KEY UPDATE col = new_values.col) as it makes complex multi-row inserts much more readable than VALUES(col).
• Idempotency: This pattern is great for creating idempotent APIs, where making the same request multiple times has the same effect as making it once.