Insert on Duplicate Key Update in MySQL         

Introduction

When inserting data into a table, you may encounter duplicate key errors if a record with the same PRIMARY KEY or UNIQUE KEY already exists.

Instead of failing or ignoring the row, MySQL provides a powerful feature:

👉 INSERT ON DUPLICATE KEY UPDATE

This allows you to insert a new row or update an existing row in one query.

What is INSERT ON DUPLICATE KEY UPDATE

This statement works as follows:

  • If no duplicate key exists → INSERT the row
  • If duplicate key exists → UPDATE the existing row

It is commonly known as an UPSERT operation.

Basic Syntax

INSERT INTO table_name (column1, column2)

VALUES (value1, value2)

ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2;

Example

INSERT INTO Students (id, name, age)

VALUES (1, 'Rahul', 20)

ON DUPLICATE KEY UPDATE

name = 'Rahul Sharma',

age = 22;

How It Works

  • If id = 1 does not exist → row is inserted
  • If id = 1 exists → name and age are updated

Using VALUES() Function

You can reuse inserted values:

INSERT INTO Students (id, name, age)

VALUES (1, 'Rahul', 20)

ON DUPLICATE KEY UPDATE

name = VALUES(name),

age = VALUES(age);

Example Scenario

Initial data:

id

name

age

1

Rahul

20

Query:

INSERT INTO Students (id, name, age)

VALUES (1, 'Rahul Sharma', 22)

ON DUPLICATE KEY UPDATE

name = VALUES(name),

age = VALUES(age);

Result:

id

name

age

1

Rahul Sharma

22

Advantages

  • Combines INSERT and UPDATE in one query
  • Avoids duplicate key errors
  • Efficient for bulk operations
  • Reduces multiple queries

Important Notes

  • Works only with PRIMARY KEY or UNIQUE constraints
  • Updates only specified columns
  • Does not delete rows (unlike REPLACE)

Comparison with Other Methods

Feature

INSERT IGNORE

REPLACE

ON DUPLICATE KEY

Duplicate handling

Skip

Delete + Insert

Update

Data loss risk

No

Yes

No

Best use

Ignore errors

Replace full row

Update existing

Common Mistakes

  • Using without a PRIMARY/UNIQUE key
  • Forgetting the UPDATE clause
  • Updating wrong columns
  • Confusing with REPLACE

Key Points to Remember

  • Performs insert or update in one query
  • Handles duplicate keys efficiently
  • Requires PRIMARY or UNIQUE key
  • Safer than REPLACE
  • Useful for real-world applications