REPLACE INTO
In this page:
REPLACE INTO table_name (column1, column2)
VALUES (value1, value2);Introduction to REPLACE INTO
REPLACE INTO works like INSERT, but with a twist for handling duplicates. If a row with the same primary key or unique index already exists, MySQL deletes the old row first and inserts the new one. If the row does not exist, it simply inserts it like a normal INSERT would.
Example: Introduction to REPLACE INTO
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users VALUES (1, 'Amit');
REPLACE INTO users VALUES (1, 'Amit Kumar');REPLACE INTO with Set Syntax
You can also use the SET syntax with the REPLACE statement instead of the column-list form. This is highly readable and works similarly to the UPDATE syntax. It makes it clear which columns are getting new values at a glance.
Example: REPLACE INTO with Set Syntax
REPLACE INTO users SET id = 1, name = 'Amit Kumar';REPLACE INTO with Select Queries
You can copy data from one table to another using REPLACE INTO with a SELECT query, useful for syncing a staging table into a live one. If duplicate keys are found in the destination table, they will be updated with the new rows automatically rather than causing an error.
Example: REPLACE INTO with Select Queries
REPLACE INTO live_users SELECT * FROM staging_users;The Behind-the-Scenes Delete
When REPLACE finds a duplicate, it deletes the old row and inserts a new one behind the scenes. This means any auto-increment primary key might change if it is not explicitly provided.
It also triggers any DELETE and INSERT triggers on the table, which can surprise you if you only expected an update.
Example: The Behind-the-Scenes Delete
-- REPLACE deletes the old row and inserts a new one; a fresh AUTO_INCREMENT value may be assigned
REPLACE INTO users (id, name) VALUES (1, 'Amit Kumar');REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE
REPLACE deletes the old row and inserts a new one, resetting every column to the values you provide. INSERT ... ON DUPLICATE KEY UPDATE updates only the specified columns of the existing row instead of deleting it.
Use REPLACE when you want to overwrite all columns easily, and the UPDATE form when you want to preserve untouched columns.
Example: REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INTO users (id, name, email) VALUES (1, 'Amit', '[email protected]'); -- resets every column
INSERT INTO users (id, name, email) VALUES (1, 'Amit', '[email protected]')
ON DUPLICATE KEY UPDATE name = 'Amit'; -- updates only name- Using
REPLACE INTOwhen you want to update a few columns, since it deletes the old row and inserts a new one, so unspecified columns go back to defaults. - Expecting it to work without a
PRIMARY KEYorUNIQUEindex, so it just inserts new rows. - Being surprised that
AUTO_INCREMENTvalues and foreign-key cascades are affected because the row is really deleted first.
UPDATEchanges existing rows, and it can set multiple columns at once.DELETEremoves rows from a table, and aWHEREclause controls which ones.REPLACE INTOinserts a row or replaces an existing one with the same key.
Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: