REPLACE is like INSERT except that it deletes old record as necessary when a duplicate unique key value is present in the new record.
With REPLACE, the new record overwrite the old record completely.
REPLACE does not support ON DUPLICATE KEY UPDATE clause.
REPLACE INTO people (id, name, age) VALUES (12, 'William', 25), (13, 'Bart', 15), (14, 'Mary', 12);
REPLACE INTO people SET id = 12, name = 'William', age = 25;
When a duplicate unique key value is present in the new record, REPLACE first deletes the old record before inserting the new record. An advantage of using REPLACE instead of an equivalent DELETE (if needed) and INSERT is that REPLACE is performed as a single atomic operation. There is no need to do any explicit table locking.
If the table does not have any unique indexes, REPLACE is equivalent to INSERT because no duplicates will ever be detected.
If the table has a unique index, and an indexed column allow NULL values, a new record with NULL value in that column does not cause a duplicate key violation, and no replacement occurs.
REPLACE returns an information string that indicates how many rows is affected. If the count is one, the row was inserted without replacing an existing row. If the count is two, a row was deleted before the new row was inserted. If the count is greater than two, it means the table has multiple unique indexes and the new record matched key values in multiple rows, resulting in multiple duplicate-key violations. This cause multiple rows to be deleted. Take a look at this table definition:
CREATE TABLE multikey ( i INT NOT NULL UNIQUE, j INT NOT NULL UNIQUE, k INT NOT NULL UNIQUE );