> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# UPSERT in MySQL

> How to do UPSERT in MySQL: INSERT ... ON DUPLICATE KEY UPDATE, REPLACE INTO, INSERT IGNORE, when to use each, and the AUTO_INCREMENT side effects to know about.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

MySQL doesn't have an `UPSERT` keyword, but it has three ways to insert a row or handle a conflict: `INSERT ... ON DUPLICATE KEY UPDATE`, `REPLACE INTO`, and `INSERT IGNORE`. They behave very differently — picking the wrong one can silently corrupt data.

## INSERT ... ON DUPLICATE KEY UPDATE

When the insert would violate a unique constraint (primary key or unique index), MySQL runs the `UPDATE` instead:

```sql theme={null}
CREATE TABLE page_views (
    page    VARCHAR(255) PRIMARY KEY,
    views   INT NOT NULL DEFAULT 0,
    last_at DATETIME
);

-- First call: inserts the row
-- Subsequent calls: increments the counter
INSERT INTO page_views (page, views, last_at)
VALUES ('/home', 1, NOW())
ON DUPLICATE KEY UPDATE
    views   = views + 1,
    last_at = NOW();
```

To reference the value from the attempted insert (rather than the existing row), use row aliases:

```sql theme={null}
INSERT INTO page_views (page, views, last_at)
VALUES ('/home', 1, NOW()) AS new
ON DUPLICATE KEY UPDATE
    page_views.views   = page_views.views + new.views,
    page_views.last_at = new.last_at;
```

The older `VALUES(col)` syntax also works but is deprecated — use row aliases instead:

```sql theme={null}
-- Deprecated — use row aliases instead
ON DUPLICATE KEY UPDATE views = views + VALUES(views)
```

## REPLACE INTO

`REPLACE` deletes the conflicting row and inserts a new one. It looks like an upsert but behaves like a delete + insert:

```sql theme={null}
REPLACE INTO page_views (page, views, last_at)
VALUES ('/home', 100, NOW());
```

This fires `BEFORE DELETE` and `AFTER DELETE` triggers on the target table and resets the `AUTO_INCREMENT` counter for the new row. Because the delete is a real row delete, `ON DELETE CASCADE` and the other foreign key actions on referencing tables DO fire. Any columns not listed in the `REPLACE` get their default values — not the previous row's values.

Use `REPLACE` only when you genuinely want to discard the old row entirely.

## INSERT IGNORE

`INSERT IGNORE` downgrades errors to warnings. On a key conflict it skips the row; on a type mismatch or truncation it writes a coerced value instead:

```sql theme={null}
INSERT IGNORE INTO page_views (page, views, last_at)
VALUES ('/home', 1, NOW());
```

The problem with `INSERT IGNORE` is that it suppresses *all* errors, not just duplicates — and the non-duplicate cases are worse than a skipped row. `INSERT IGNORE INTO page_views (page, views) VALUES ('/new', 'not_a_number')` inserts `('/new', 0)` with only a warning: a silent, corrupted write rather than a no-op. Use it only when you genuinely don't care about the outcome.

## Comparison

| | `ON DUPLICATE KEY UPDATE` | `REPLACE INTO` | `INSERT IGNORE` |
| :- | :- | :- | :- |
| On conflict | Updates specified columns | Deletes + inserts | Discards the row |
| Preserves existing values | Yes (you control what's updated) | No — unspecified columns reset to defaults | N/A (no update) |
| Triggers fired | UPDATE triggers | DELETE + INSERT triggers on target table | No triggers on conflict |
| Foreign key cascades | No | Yes — the internal DELETE fires them | No |
| AUTO\_INCREMENT | Increments on conflict | New increment for the inserted row | No increment |
| Use when | You need to update specific fields | You want to fully replace the row | You want to silently skip duplicates |

## AUTO\_INCREMENT Side Effect

`ON DUPLICATE KEY UPDATE` increments the `AUTO_INCREMENT` counter even when it takes the update path, not the insert path. Over time this creates gaps in the sequence. `page_views` uses `page` as its primary key and has no `AUTO_INCREMENT` column, so to see the effect, use a table with a surrogate key:

```sql theme={null}
CREATE TABLE page_views_by_id (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    page  VARCHAR(255) UNIQUE,
    views INT DEFAULT 0
);
INSERT INTO page_views_by_id (page, views) VALUES ('/home', 1);
INSERT INTO page_views_by_id (page, views) VALUES ('/home', 2) AS new
    ON DUPLICATE KEY UPDATE views = new.views;  -- updates the existing row

SHOW CREATE TABLE page_views_by_id;  -- AUTO_INCREMENT has advanced anyway
```

This is expected behavior. If your application depends on a gap-free sequence, use a separate counter table or handle sequence generation in the application.

## Frequently Asked Questions

#### Is ON DUPLICATE KEY UPDATE atomic?

Yes — the insert attempt and the update happen in a single atomic operation. You don't need to wrap it in a transaction to avoid a race condition between checking existence and inserting.

#### What if multiple unique indexes conflict?

If an insert violates more than one unique constraint simultaneously, MySQL updates only one row — which one is undefined. Avoid designs where a single INSERT can conflict on multiple unique indexes. If your schema has overlapping unique constraints, test the behavior carefully.

#### Can I use ON DUPLICATE KEY UPDATE with multi-row inserts?

Yes:

```sql theme={null}
INSERT INTO page_views (page, views)
VALUES ('/home', 1), ('/about', 1), ('/contact', 1)
ON DUPLICATE KEY UPDATE views = views + 1;
```

Each conflicting row triggers its own update. With row aliases, use `VALUES` for the multi-row case and `AS new` for the alias.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `ON DUPLICATE KEY UPDATE` not triggering | The conflict column must be a PRIMARY KEY or UNIQUE index — regular indexes don't trigger it |
| `REPLACE` clearing columns you didn't specify | Expected behavior — REPLACE is delete + insert; specify all columns or use ON DUPLICATE KEY UPDATE |
| `INSERT IGNORE` hiding real errors | Remove IGNORE, reproduce the error, and fix the root cause — IGNORE masks all errors, not just duplicates |
| AUTO\_INCREMENT gaps growing large | Normal side effect of ON DUPLICATE KEY UPDATE; gaps in AUTO\_INCREMENT are cosmetic and don't affect correctness |

## See also

* [Transactions in MySQL](https://villagesql.com/docs/en/guides/transactions.md) — when to wrap UPSERT in a transaction
* [Deadlocks in MySQL](https://villagesql.com/docs/en/guides/deadlocks.md) — INSERT ... ON DUPLICATE KEY UPDATE can cause deadlocks under concurrency
* [Bulk Inserts in MySQL](https://villagesql.com/docs/en/guides/bulk-inserts.md) — loading many rows fast, with or without conflict handling
* [Common MySQL Errors and How to Fix Them](https://villagesql.com/docs/en/guides/common-mysql-errors.md) — duplicate-key errors and what they mean
* [MySQL INSERT ON DUPLICATE KEY UPDATE](https://villagesql.com/docs/en/tutorial/upsert.md) — the statement on its own, with worked examples


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.