ON DUPLICATE KEY UPDATE in MySQL

Introduction

This helps you add a new row or update an existing row if a duplicate key is found. It saves time by doing both in one command.

When you want to add a new user but update their info if they already exist.

When inserting sales data and updating the total if the record is already there.

When logging events and you want to increase a count if the event happened before.

When syncing data from another system and you want to avoid duplicate entries.

Syntax

MySQL

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

The duplicate key is usually a PRIMARY KEY or UNIQUE index.

You can update one or more columns after the duplicate key is found.

Examples

If user with id=1 exists, update age to 30; otherwise, add new user.

MySQL

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30) ON DUPLICATE KEY UPDATE age = VALUES(age);

Insert product or update price if SKU already exists.

MySQL

INSERT INTO products (sku, price) VALUES ('A123', 10.99) ON DUPLICATE KEY UPDATE price = VALUES(price);

Add 100 to quantity if item_id 5 exists; otherwise, insert new row.

MySQL

INSERT INTO inventory (item_id, quantity) VALUES (5, 100) ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

Sample Program

This creates a users table, inserts Alice with age 25, then tries to insert Alice again with age 30. Because id=1 exists, it updates age to 30. Finally, it selects Alice's record.

MySQL

CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30) ON DUPLICATE KEY UPDATE age = VALUES(age);

SELECT * FROM users WHERE id = 1;

Important Notes

Make sure the table has a PRIMARY KEY or UNIQUE index for this to work.

You can use VALUES(column) in the UPDATE part to refer to the new value.

This is a quick way to avoid separate SELECT checks before INSERT or UPDATE.

Summary

Use ON DUPLICATE KEY UPDATE to insert or update in one step.

It works when a duplicate key (like PRIMARY KEY) is found.

You can update one or more columns with new values or calculations.