INSERT IGNORE & ON DUPLICATE KEY UPDATE0%

INSERT IGNORE & ON DUPLICATE KEY UPDATE

Beginner12 min read•Updated: 2026-09-12

INSERT IGNORE & ON DUPLICATE KEY UPDATE: Upserting in MySQL

When synchronizing data feeds, importing large CSV files, or tracking user website activity, you will frequently encounter records that already exist in your database. Standard INSERT statements fail completely on duplicate key collisions, aborting entire batches. MySQL provides two powerful solutions: INSERT IGNORE and ON DUPLICATE KEY UPDATE (Upserting).

1. The Duplicate Key Collision Problem

Suppose we have a user analytics table where user_id is unique:

1CREATE TABLE user_daily_logins (
2    user_id INT PRIMARY KEY,
3    login_count INT NOT NULL DEFAULT 1,
4    last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
5);

If you run:

1INSERT INTO user_daily_logins (user_id) VALUES (101); -- Succeeds!
2INSERT INTO user_daily_logins (user_id) VALUES (101); -- CRASHES with Error 1062: Duplicate entry!

2. Silent Skipping with `INSERT IGNORE`

When you use INSERT IGNORE, MySQL silently skips any row that would cause a duplicate key violation (or invalid data type error), downgrading errors to harmless warnings:

1-- Attempting to insert 3 users. User 101 already exists:
2INSERT IGNORE INTO user_daily_logins (user_id)
3VALUES 
4    (101), -- Skipped silently!
5    (102), -- Inserted!
6    (103); -- Inserted!

Rows that cause duplicate key violations are ignored with zero errors.
Clean rows are inserted normally.
Limitation: The existing row is not updated or modified in any way.

3. The Power of Upsert: `ON DUPLICATE KEY UPDATE`

In software engineering, an "Upsert" (Update or Insert) is an operation that:

1
Inserts a new record if the primary/unique key does not exist.
2
Updates the existing record if the primary/unique key does exist!

1-- Daily Login Tracker: If user exists, increment login count and update timestamp!
2INSERT INTO user_daily_logins (user_id, login_count, last_login)
3VALUES (101, 1, NOW())
4ON DUPLICATE KEY UPDATE
5    login_count = login_count + 1,
6    last_login = NOW();

Modern MySQL 8.0.19+ Alias Syntax:

In MySQL 8.0.19+, you can use table and column aliases directly in the update clause:

1INSERT INTO user_daily_logins (user_id, login_count, last_login)
2VALUES (101, 1, NOW()) AS new_row
3ON DUPLICATE KEY UPDATE
4    login_count = user_daily_logins.login_count + new_row.login_count,
5    last_login = new_row.last_login;

4. What About `REPLACE INTO`? (The Hidden Trap)

MySQL also supports REPLACE INTO, but DBAs consider it a dangerous anti-pattern:

REPLACE INTO does not update in-place.
It physically DELETES the old row and INSERTS a brand new row!
Dangers:

1
It triggers ON DELETE CASCADE on foreign key child tables, unintentionally wiping related records!
2
It fires DELETE triggers.
3
It increments the AUTO_INCREMENT sequence unnecessarily.

Always prefer INSERT ... ON DUPLICATE KEY UPDATE over REPLACE INTO!

5. Best Practices & Common Pitfalls

Ensure Unique Index Exists: ON DUPLICATE KEY UPDATE only activates if the collision occurs on a PRIMARY KEY or UNIQUE index. It has zero effect on standard non-unique columns.
Inspect ROW_COUNT() Return Codes:
If a row is newly inserted, MySQL reports 1 row affected.
If an existing row is updated, MySQL reports 2 rows affected!
If an existing row is matched but no values changed, MySQL reports 0 rows affected.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the term "Upsert" signify in database management?

Question 2

How does INSERT IGNORE handle a row that violates a PRIMARY KEY constraint during batch insertion?

Question 3

What is the fundamental danger of using REPLACE INTO instead of INSERT ... ON DUPLICATE KEY UPDATE?

Question 4

What row count does MySQL return when ON DUPLICATE KEY UPDATE successfully modifies an existing record?

Question 5

On which types of columns does ON DUPLICATE KEY UPDATE detect collisions?

Next Lesson

Modifying Records with UPDATE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Inserting Records with INSERT INTO	Modifying Records with UPDATE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Modifying Records with UPDATE

INSERT IGNORE & ON DUPLICATE KEY UPDATE

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

INSERT IGNORE & ON DUPLICATE KEY UPDATE: Upserting in MySQL

1. The Duplicate Key Collision Problem

Suppose we have a user analytics table where user_id is unique:

1CREATE TABLE user_daily_logins (
2    user_id INT PRIMARY KEY,
3    login_count INT NOT NULL DEFAULT 1,
4    last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
5);

If you run:

1INSERT INTO user_daily_logins (user_id) VALUES (101); -- Succeeds!
2INSERT INTO user_daily_logins (user_id) VALUES (101); -- CRASHES with Error 1062: Duplicate entry!

2. Silent Skipping with `INSERT IGNORE`

When you use INSERT IGNORE, MySQL silently skips any row that would cause a duplicate key violation (or invalid data type error), downgrading errors to harmless warnings:

1-- Attempting to insert 3 users. User 101 already exists:
2INSERT IGNORE INTO user_daily_logins (user_id)
3VALUES 
4    (101), -- Skipped silently!
5    (102), -- Inserted!
6    (103); -- Inserted!

Rows that cause duplicate key violations are ignored with zero errors.
Clean rows are inserted normally.
Limitation: The existing row is not updated or modified in any way.

3. The Power of Upsert: `ON DUPLICATE KEY UPDATE`

In software engineering, an "Upsert" (Update or Insert) is an operation that:

1
Inserts a new record if the primary/unique key does not exist.
2
Updates the existing record if the primary/unique key does exist!

1-- Daily Login Tracker: If user exists, increment login count and update timestamp!
2INSERT INTO user_daily_logins (user_id, login_count, last_login)
3VALUES (101, 1, NOW())
4ON DUPLICATE KEY UPDATE
5    login_count = login_count + 1,
6    last_login = NOW();

Modern MySQL 8.0.19+ Alias Syntax:

In MySQL 8.0.19+, you can use table and column aliases directly in the update clause:

1INSERT INTO user_daily_logins (user_id, login_count, last_login)
2VALUES (101, 1, NOW()) AS new_row
3ON DUPLICATE KEY UPDATE
4    login_count = user_daily_logins.login_count + new_row.login_count,
5    last_login = new_row.last_login;

4. What About `REPLACE INTO`? (The Hidden Trap)

MySQL also supports REPLACE INTO, but DBAs consider it a dangerous anti-pattern:

REPLACE INTO does not update in-place.
It physically DELETES the old row and INSERTS a brand new row!
Dangers:

1
It triggers ON DELETE CASCADE on foreign key child tables, unintentionally wiping related records!
2
It fires DELETE triggers.
3
It increments the AUTO_INCREMENT sequence unnecessarily.

Always prefer INSERT ... ON DUPLICATE KEY UPDATE over REPLACE INTO!

5. Best Practices & Common Pitfalls

Ensure Unique Index Exists: ON DUPLICATE KEY UPDATE only activates if the collision occurs on a PRIMARY KEY or UNIQUE index. It has zero effect on standard non-unique columns.
Inspect ROW_COUNT() Return Codes:
If a row is newly inserted, MySQL reports 1 row affected.
If an existing row is updated, MySQL reports 2 rows affected!
If an existing row is matched but no values changed, MySQL reports 0 rows affected.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the term "Upsert" signify in database management?

Question 2

How does INSERT IGNORE handle a row that violates a PRIMARY KEY constraint during batch insertion?

Question 3

What is the fundamental danger of using REPLACE INTO instead of INSERT ... ON DUPLICATE KEY UPDATE?

Question 4

What row count does MySQL return when ON DUPLICATE KEY UPDATE successfully modifies an existing record?

Question 5

On which types of columns does ON DUPLICATE KEY UPDATE detect collisions?

Next Lesson

Modifying Records with UPDATE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Inserting Records with INSERT INTO	Modifying Records with UPDATE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

INSERT IGNORE & ON DUPLICATE KEY UPDATE: Upserting in MySQL

1. The Duplicate Key Collision Problem

2. Silent Skipping with INSERT IGNORE

3. The Power of Upsert: ON DUPLICATE KEY UPDATE

Modern MySQL 8.0.19+ Alias Syntax:

4. What About REPLACE INTO? (The Hidden Trap)

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Modifying Records with UPDATE

Related Lessons

Practice Quiz

Loading Page Content...

INSERT IGNORE & ON DUPLICATE KEY UPDATE: Upserting in MySQL

1. The Duplicate Key Collision Problem

2. Silent Skipping with INSERT IGNORE

3. The Power of Upsert: ON DUPLICATE KEY UPDATE

Modern MySQL 8.0.19+ Alias Syntax:

4. What About REPLACE INTO? (The Hidden Trap)

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Modifying Records with UPDATE

Related Lessons

Practice Quiz

2. Silent Skipping with `INSERT IGNORE`

3. The Power of Upsert: `ON DUPLICATE KEY UPDATE`

4. What About `REPLACE INTO`? (The Hidden Trap)

2. Silent Skipping with `INSERT IGNORE`

3. The Power of Upsert: `ON DUPLICATE KEY UPDATE`

4. What About `REPLACE INTO`? (The Hidden Trap)