MySQL Upsert Logic – INSERT or UPDATE in One Command

Introduction – Why Use Upsert in MySQL?

In MySQL, upsert logic allows you to insert a new row or update an existing one if a duplicate key is found. It’s commonly needed when:

• Inserting new records with fallback update
• Maintaining counters or aggregates
• Syncing data from external systems
• Avoiding duplication while preserving integrity

In this guide, you’ll learn:

• How to implement upsert logic using INSERT ... ON DUPLICATE KEY UPDATE
• Use of REPLACE INTO and its caveats
• Practical examples and best practices

1. INSERT … ON DUPLICATE KEY UPDATE

This is the preferred method of implementing upserts in MySQL.

Syntax

INSERT INTO table (columns...)
VALUES (values...)
ON DUPLICATE KEY UPDATE column = value;

Example – Insert or Update Product Stock

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

If product_id = 101 exists, quantity increases by 5
If not, a new row is inserted

Requirements

• Table must have a PRIMARY KEY or UNIQUE KEY
• The ON DUPLICATE KEY UPDATE clause only triggers if there’s a duplicate violation

2. REPLACE INTO – Alternative Upsert

This command first deletes the existing row and then inserts the new one.

Syntax

REPLACE INTO table (columns...)
VALUES (values...);

Example

REPLACE INTO users (id, name)
VALUES (1, 'Alice');

If id = 1 exists, the row is deleted and reinserted

Caution: Auto-increment, foreign keys, and triggers may behave unexpectedly.

INSERT vs REPLACE vs UPSERT

Real-World Use Case – Track Login Timestamps

CREATE TABLE login_audit (
  user_id INT PRIMARY KEY,
  last_login DATETIME
);

INSERT INTO login_audit (user_id, last_login)
VALUES (123, NOW())
ON DUPLICATE KEY UPDATE last_login = NOW();

Logs login time whether it’s the first login or a repeat.

Best Practices

Advanced Example – Maintain Aggregate Score

INSERT INTO leaderboard (user_id, score)
VALUES (7, 10)
ON DUPLICATE KEY UPDATE score = score + 10;

Increases score if user exists, otherwise inserts new user.

Common Use Cases

Summary – Recap & Next Steps

MySQL’s upsert logic using INSERT ... ON DUPLICATE KEY UPDATE and REPLACE INTO enables efficient conflict-free inserts and updates in a single command.

Key Takeaways

• Use INSERT ... ON DUPLICATE KEY UPDATE for safe, non-destructive upserts
• Use REPLACE INTO only when you’re okay with delete-then-insert behavior
• Ensure a UNIQUE or PRIMARY KEY exists on conflict columns
• Upsert logic reduces the need for complex SELECT before INSERT patterns

Real-World Relevance

Used in user tracking, product syncing, queue systems, activity logging, and web applications with high-frequency inserts and updates.

FAQ – MySQL Upsert Logic

Does ON DUPLICATE KEY UPDATE require a unique key?

Yes. It triggers only when a PRIMARY KEY or UNIQUE INDEX conflict occurs.

Is REPLACE INTO the same as UPSERT?

No. REPLACE INTO deletes and reinserts the row—it’s more like DELETE + INSERT.

Can I update multiple columns on conflict?

Yes:

ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2);

Can I use ON DUPLICATE KEY without a primary or unique key?

No. Without a conflict condition, the UPDATE clause won’t trigger.

Is MERGE available in MySQL?

No. MERGE is not part of MySQL. Use upsert logic instead.