REPLACE INTO

REPLACE works like INSERT, with one difference: if the new row matches an existing row on a PRIMARY KEY or UNIQUE index, MySQL deletes the old row first and then inserts the new one. It is MySQL-specific.

1
2
3
4
5
6

CREATE TABLE settings (
    name VARCHAR(30) PRIMARY KEY,
    value VARCHAR(50) NOT NULL,
    note VARCHAR(50) NULL
);
INSERT INTO settings VALUES ('theme', 'light', 'chosen by user');

1
2

REPLACE INTO settings (name, value) VALUES ('theme', 'dark');
SELECT * FROM settings;

1
2
3
4
5
6
7
8

Query OK, 2 rows affected

+-------+-------+------+
| name  | value | note |
+-------+-------+------+
| theme | dark  | NULL |
+-------+-------+------+
1 row in set

2 rows affected means one row was deleted and one inserted. And look at the result: the note column is NULL now. REPLACE does not update the old row. It creates a brand-new row, and every column you did not mention gets its default.

Compare with the upsert

1
2
3

INSERT INTO settings (name, value, note) VALUES ('mode', 'compact', 'first note');
INSERT INTO settings (name, value) VALUES ('mode', 'wide') ON DUPLICATE KEY UPDATE value = 'wide';
SELECT * FROM settings ORDER BY name;

1
2
3
4
5
6
7

+-------+-------+------------+
| name  | value | note       |
+-------+-------+------------+
| mode  | wide  | first note |
| theme | dark  | NULL       |
+-------+-------+------------+
2 rows in set

With ON DUPLICATE KEY UPDATE the note is kept. The upsert changes columns, while REPLACE rebuilds the row.

Side effects of REPLACE

Because it is a delete followed by an insert:

1. AUTO_INCREMENT values change. A replaced row gets a new ID.
2. Foreign keys. A delete can be blocked by child rows, or can cascade-delete the children (ON DELETE CASCADE).
3. Triggers. DELETE and INSERT triggers run, not UPDATE triggers.
4. Columns not listed are reset to their defaults, as you saw.
5. It touches every unique index, so one REPLACE may delete several different old rows.

1
2
3
4

CREATE TABLE authors (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50) UNIQUE, name VARCHAR(30));
INSERT INTO authors (email, name) VALUES ('a@example.com', 'Asha');
REPLACE INTO authors (email, name) VALUES ('a@example.com', 'Asha R.');
SELECT * FROM authors;

1
2
3
4
5
6

+----+---------------+---------+
| id | email         | name    |
+----+---------------+---------+
|  2 | a@example.com | Asha R. |
+----+---------------+---------+
1 row in set

The author’s id changed from 1 to 2. Any table that stored author_id = 1 is now pointing to nothing, or the rows were deleted by a cascade.

When REPLACE is fine

• A simple lookup or cache table with no foreign keys, where every column is always provided and the row ID has no meaning.

What to use instead

For almost every real case, choose INSERT ... ON DUPLICATE KEY UPDATE (Upsert), which keeps the row and its ID.