REPLACE & INSERT ... ON DUPLICATE KEY — MySQL

REPLACE INTO in MySQL

REPLACE INTO is a MySQL-specific extension that combines delete and insert into a single statement. When the new row would cause a duplicate key violation on a PRIMARY KEY or UNIQUE index, MySQL deletes the conflicting row and inserts the new one in its place. If no duplicate exists, it behaves exactly like a regular INSERT.

Understanding exactly what REPLACE does — and when to avoid it — is crucial because it has surprising side-effects that trip up experienced developers.

Basic Syntax

SQL

-- VALUES form
REPLACE INTO settings (key_name, value)
VALUES ('theme', 'dark');

-- SET form (more readable for single rows)
REPLACE INTO settings
SET key_name = 'language',
    value    = 'en';

-- Multi-row REPLACE
REPLACE INTO settings (key_name, value)
VALUES
  ('theme',    'dark'),
  ('language', 'en'),
  ('timezone', 'UTC');

-- REPLACE ... SELECT (copy from another table, replacing conflicts)
REPLACE INTO products (id, sku, name, price)
SELECT id, sku, name, price
FROM products_staging;

How REPLACE Works Internally

REPLACE is not an atomic update. Internally MySQL performs exactly these steps:

Attempt to INSERT the new row.
If a duplicate-key error occurs on any PRIMARY KEY or UNIQUE constraint, DELETE the conflicting row(s).
INSERT the new row again.

This means REPLACE is a DELETE followed by an INSERT — never an in-place UPDATE. This distinction has three significant consequences: trigger firing order, AUTO_INCREMENT behaviour, and the fate of columns you did not specify.

SQL

CREATE TABLE user_prefs (
  user_id   INT UNSIGNED NOT NULL,
  theme     VARCHAR(20)  NOT NULL DEFAULT 'light',
  font_size TINYINT      NOT NULL DEFAULT 14,
  PRIMARY KEY (user_id)
);

INSERT INTO user_prefs (user_id, theme, font_size)
VALUES (1, 'dark', 18);
-- Row: user_id=1, theme='dark', font_size=18

-- REPLACE specifying only theme (not font_size)
REPLACE INTO user_prefs (user_id, theme)
VALUES (1, 'solarized');
-- Step 1: INSERT fails -- duplicate user_id=1
-- Step 2: DELETE old row (user_id=1, theme='dark', font_size=18)
-- Step 3: INSERT new row (user_id=1, theme='solarized', font_size=DEFAULT=14)
-- Result: font_size silently reset from 18 back to 14!

Warning

Any column you omit from the REPLACE statement is reset to its DEFAULT value, not kept from the original row. This silent data loss is the most common REPLACE bug.

REPLACE vs INSERT ON DUPLICATE KEY UPDATE — Deep Comparison

Dimension	REPLACE INTO	INSERT ... ON DUPLICATE KEY UPDATE
Internal mechanism	DELETE old + INSERT new	UPDATE existing row in-place
AUTO_INCREMENT	New ID always generated	Existing ID preserved
Unspecified columns	Reset to column DEFAULT	Left unchanged (kept as-is)
BEFORE DELETE trigger	Fires on the old row	Never fires
AFTER DELETE trigger	Fires on the old row	Never fires
BEFORE INSERT trigger	Fires for the new row	Never fires
AFTER INSERT trigger	Fires for the new row	Never fires
BEFORE UPDATE trigger	Never fires	Fires on a duplicate
AFTER UPDATE trigger	Never fires	Fires on a duplicate
FK child rows (CASCADE)	May cascade-delete children	Children unaffected
ROW_COUNT() return	2 for a replace, 1 for plain insert	1 for insert, 2 for update
Audit log entries	2 entries per replace (delete + insert)	1 entry per update
Performance	Slightly slower (2 InnoDB ops)	Faster (1 in-place write)

Tip

In almost every real-world use case INSERT ... ON DUPLICATE KEY UPDATE is the correct choice. Use REPLACE only when you genuinely need full-row replacement semantics with a natural key.

INSERT ON DUPLICATE KEY UPDATE — The Preferred Upsert

SQL

-- Preferred pattern: update only what changed, leave the rest intact
INSERT INTO user_prefs (user_id, theme, font_size)
VALUES (1, 'solarized', 18)
ON DUPLICATE KEY UPDATE
  theme     = VALUES(theme),
  font_size = VALUES(font_size);
-- If user_id=1 exists: updates theme and font_size only
-- If user_id=1 is new: inserts the complete row

-- MySQL 8.0+ alias syntax (cleaner than VALUES())
INSERT INTO user_prefs (user_id, theme, font_size)
VALUES (1, 'solarized', 18) AS new
ON DUPLICATE KEY UPDATE
  theme     = new.theme,
  font_size = new.font_size;

REPLACE and AUTO_INCREMENT — New ID Every Time

One of the most dangerous REPLACE behaviours involves AUTO_INCREMENT primary keys. Because REPLACE deletes the old row and inserts a new one, the new row always gets a fresh AUTO_INCREMENT value. The original ID is gone forever.

SQL

CREATE TABLE articles (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug    VARCHAR(100) NOT NULL,
  title   VARCHAR(255) NOT NULL,
  UNIQUE KEY uq_slug (slug)
);

INSERT INTO articles (slug, title) VALUES ('hello-world', 'Hello World');
-- Row: id=1, slug='hello-world', title='Hello World'

REPLACE INTO articles (slug, title) VALUES ('hello-world', 'Hello World Updated');
-- Step 1: INSERT fails -- duplicate slug
-- Step 2: DELETE id=1
-- Step 3: INSERT new row -- gets id=2

SELECT * FROM articles;
-- +----+-------------+---------------------+
-- | id | slug        | title               |
-- +----+-------------+---------------------+
-- |  2 | hello-world | Hello World Updated |
-- +----+-------------+---------------------+
-- id=1 is gone. AUTO_INCREMENT counter now at 3.

Warning

If any other table holds a foreign key to articles.id, deleting id=1 will either orphan those rows or trigger CASCADE deletes depending on the FK definition — even though you only intended to update the title.

Multi-Row REPLACE and Performance

SQL

-- Multi-row REPLACE is efficient -- one round-trip to the server
REPLACE INTO kv_store (k, v, expires_at)
VALUES
  ('session:abc', 'data1', DATE_ADD(NOW(), INTERVAL 1 HOUR)),
  ('session:def', 'data2', DATE_ADD(NOW(), INTERVAL 1 HOUR)),
  ('session:ghi', 'data3', DATE_ADD(NOW(), INTERVAL 1 HOUR));

-- Each row is processed independently:
-- a row with no conflict: 1 INSERT operation
-- a row with a conflict: 1 DELETE + 1 INSERT = 2 operations
-- ROW_COUNT() reflects the total: 3 inserts = 3, 3 replaces = 6, mixed = somewhere in between

Note

For bulk-loading data into a table with a natural key, REPLACE is a legitimate pattern. For tables with AUTO_INCREMENT or FK children, it is almost always the wrong tool.

REPLACE with Generated Columns

SQL

CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  price_cents INT AS (ROUND(price * 100)) STORED,
  UNIQUE KEY uq_name (name)
);

-- Generated columns are computed automatically on INSERT/REPLACE
REPLACE INTO products (name, price) VALUES ('Widget', 9.99);
-- price_cents will be 999 automatically
-- But if this replaces an existing row, it gets a NEW id (auto-inc issue applies)

Ideal Use Case — Key-Value Cache Table

REPLACE is well-suited for key-value configuration or cache tables where:

The primary key is a natural string key (no AUTO_INCREMENT).
There are no child tables referencing rows via foreign keys.
You always want the entire row replaced with fresh data.
DELETE triggers have no meaningful side effects.

SQL

CREATE TABLE app_config (
  config_key   VARCHAR(255) NOT NULL,
  config_value TEXT         NOT NULL,
  updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
               ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (config_key)
) ENGINE=InnoDB;

-- Fully replace config entries (natural key, no children, no auto-inc)
REPLACE INTO app_config (config_key, config_value)
VALUES ('maintenance_mode', 'false'),
       ('max_upload_mb',    '50'),
       ('feature_dark_mode','true');

-- This is safe because:
-- 1. config_key is the PK (no AUTO_INCREMENT side-effect)
-- 2. No other table FKs into app_config
-- 3. We always supply all columns
-- 4. Full replacement is the intended semantics

ETL and Idempotent Data Loads

SQL

-- Pattern: nightly ETL that imports dimension data
-- Running the same load twice should produce the same result (idempotent)
CREATE TABLE dim_country (
  country_code CHAR(2)      NOT NULL,
  country_name VARCHAR(100) NOT NULL,
  region       VARCHAR(50)  NOT NULL,
  PRIMARY KEY (country_code)
);

-- Running this repeatedly is safe -- each run produces the same state
REPLACE INTO dim_country (country_code, country_name, region)
SELECT country_code, country_name, region
FROM staging_country_data;

Tip

For ETL pipelines with surrogate-key tables (AUTO_INCREMENT), use INSERT ... ON DUPLICATE KEY UPDATE instead to avoid destroying and regenerating IDs on every load run.

Trigger Side Effects in Detail

SQL

-- Assume articles has these triggers:
-- BEFORE INSERT, AFTER INSERT, BEFORE DELETE, AFTER DELETE, BEFORE UPDATE, AFTER UPDATE

-- Plain INSERT (no conflict) fires:
--   BEFORE INSERT -> AFTER INSERT

-- REPLACE with a conflict fires:
--   BEFORE DELETE -> AFTER DELETE -> BEFORE INSERT -> AFTER INSERT
-- Note: BEFORE/AFTER UPDATE never fires -- REPLACE is not an UPDATE

-- Consequence: an audit_log trigger will write TWO records per replace:
-- one for the delete and one for the insert.
-- If your audit_log tracks row state changes, this creates a false
-- "deletion" event followed by a "creation" event instead of a clean "update" event.

Warning

If your table has audit triggers, notification triggers, or statistics aggregation triggers, REPLACE fires the wrong trigger events and will produce incorrect audit trails. Use INSERT ... ON DUPLICATE KEY UPDATE to fire UPDATE triggers instead.

ROW_COUNT() to Detect Replace vs Insert

SQL

REPLACE INTO settings (key_name, value) VALUES ('theme', 'dark');
SELECT ROW_COUNT() AS affected;
-- Returns 1: a plain INSERT occurred (no conflict)
-- Returns 2: a REPLACE occurred (DELETE + INSERT)

-- In application code you can branch on this:
-- if affected_rows == 1: a new entry was created
-- if affected_rows == 2: an existing entry was replaced

MySQL 8.0 Notes

REPLACE behaviour is unchanged in MySQL 8.0 — all the warnings above still apply.
MySQL 8.0 introduced the cleaner alias syntax for ON DUPLICATE KEY UPDATE, reducing the need for REPLACE even further.
Atomic DDL in MySQL 8.0 does not affect REPLACE (it is DML, not DDL).
If you are using MySQL 8.0+ consider using INSERT ... ON DUPLICATE KEY UPDATE with the new alias syntax for all upsert needs.
Group Replication (MySQL 8.0) may have additional restrictions around REPLACE with auto-increment in multi-master setups.

Practical Bulk-Upsert Pattern

SQL

-- Recommended pattern for bulk upsert (not REPLACE) on an auto-inc table
INSERT INTO products (id, sku, name, price, stock)
VALUES
  (101, 'W-001', 'Widget',  9.99, 100),
  (102, 'G-001', 'Gadget', 19.99,  50),
  (103, 'T-001', 'Trinket', 4.99, 200)
ON DUPLICATE KEY UPDATE
  sku   = VALUES(sku),
  name  = VALUES(name),
  price = VALUES(price),
  stock = VALUES(stock);
-- Existing rows: IDs preserved, columns updated
-- New rows: inserted with the specified IDs

Best Practices Summary

Default to INSERT ... ON DUPLICATE KEY UPDATE for all upsert operations — it is safer in every dimension.
Only use REPLACE when working with natural-key tables (no AUTO_INCREMENT, no FK children, all columns specified).
Never use REPLACE on a table that has child tables with FK references — it will silently delete children via CASCADE.
Be aware that REPLACE fires DELETE and INSERT triggers, not UPDATE triggers — audit tables will show false delete/create pairs.
In multi-row REPLACE, ROW_COUNT() reflects total operations (inserts + delete+insert pairs), not just matching rows.
Document clearly in schema comments whenever REPLACE is used intentionally so future developers understand the semantics.
Test REPLACE in a staging environment after any schema change that adds columns or FK constraints.