mysql-insert-on-duplicate-key-update

MySQL: The “Upsert” Operation with ON DUPLICATE KEY UPDATE

A common challenge in application development is needing to insert a new row, but if a row with the same unique key already exists, update it instead. This is often called an “upsert” operation. Performing this as a separate SELECT followed by an INSERT or UPDATE is inefficient and can lead to race conditions in concurrent environments. MySQL provides a powerful, atomic solution: the ON DUPLICATE KEY UPDATE clause.

The Core Concept

You use this clause with a standard INSERT statement. For it to work, the table must have a PRIMARY KEY or a UNIQUE index. When you try to insert a row that would cause a duplicate value in that key or index, the UPDATE part of the clause is executed instead of the query failing with an error.

Basic Syntax

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3)
ON DUPLICATE KEY UPDATE
    col2 = new_val2,
    col3 = new_val3;

Practical Example: Tracking Page Views

A perfect use case for ON DUPLICATE KEY UPDATE is tracking statistics, like page views on a website. Let’s create a table to store these counts.

CREATE TABLE page_views (
    page_slug VARCHAR(255) NOT NULL PRIMARY KEY,
    view_count INT UNSIGNED NOT NULL DEFAULT 1,
    last_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Now, every time a page is viewed, we can run a single, efficient query. If it’s the first view for /about-us, it will insert a new row with view_count = 1. If the row already exists, it will increment the view_count by 1.

INSERT INTO page_views (page_slug)
VALUES ('/about-us')
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

After running this query multiple times, you can check the result:

SELECT * FROM page_views WHERE page_slug = '/about-us';

page_slug	view_count	last_viewed_at
/about-us	5	YYYY-MM-DD HH:MM:SS

Modern Syntax: Using the VALUES() Function and Aliases

When updating, you often want to use the values you tried to insert. The VALUES() function lets you reference these. For example, let’s update a user profile, inserting if the user doesn’t exist.

-- Let's create a simple user profile table
CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,
  name VARCHAR(100),
  last_login TIMESTAMP
);

-- Upsert query using VALUES()
INSERT INTO user_profiles (user_id, name, last_login)
VALUES (101, 'Alice', NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    last_login = VALUES(last_login);

In MySQL 8.0.19+, you can use row and column aliases for the new row, which is more readable and the recommended approach:

-- Recommended modern syntax
INSERT INTO user_profiles (user_id, name, last_login)
VALUES (101, 'Alice Smith', NOW()) AS new_values
ON DUPLICATE KEY UPDATE
    name = new_values.name,
    last_login = new_values.last_login;

Understanding Affected Rows

The number of affected rows returned by this statement can be surprising:

• 1 is returned for a successful INSERT.
• 2 is returned for a successful UPDATE (conceptually, one row is deleted and a new one is inserted).
• 0 is returned if an existing row was matched but the UPDATE clause resulted in no change to its values.

Client Program: Securely Upserting Data

When building an upsert query in your application, all user-provided data must be passed through parameterized queries.

Node.js (mysql2): Updating Product Inventory

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

async function upsertProduct(product) {
  let connection;
  try {
    connection = await mysql.createConnection({ host: 'localhost', user: 'root', password: 'my-secret-pw', database: 'your_db' });

    const query = `
      INSERT INTO products (id, name, quantity, updated_at)
      VALUES (?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        quantity = VALUES(quantity),
        updated_at = NOW()
    `;

    const [result] = await connection.execute(query, [product.id, product.name, product.quantity]);
    console.log(`Upsert successful. Affected rows: ${result.affectedRows}`);

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

upsertProduct({ id: 'SKU-123', name: 'Wireless Keyboard', quantity: 50 });

Python (mysql-connector-python): Upserting User Scores

import mysql.connector

def upsert_user_score(user_id, score, name):
    query = """
        INSERT INTO user_scores (user_id, name, high_score)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE
            name = VALUES(name),
            high_score = GREATEST(high_score, VALUES(high_score))
    """
    # GREATEST() ensures we only update if the new score is higher
    try:
        conn = mysql.connector.connect(user='root', password='my-secret-pw', database='your_db')
        cursor = conn.cursor()

        cursor.execute(query, (user_id, name, score))
        conn.commit()
        print(f"Upserted score for user {user_id}. Rows affected: {cursor.rowcount}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    upsert_user_score(505, 9800, 'Alex')