A Quick Overview of the REPLACE Statement in MySQL

In MySQL, we can use the REPLACE statement to insert data if we think that some rows need to replace existing rows.

The REPLACE statement works just like the INSERT statement, except that if it contains rows with the same PRIMARY KEY or UNIQUE index as those in the table, then it replaces those rows in the table. It does this by deleting the old row and then inserting the new row.

To use the REPLACE statement, we must have both the INSERT and DELETE privileges for the table.

Example

Suppose we create a table and insert data:

CREATE TABLE Person
(
    PersonId INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

INSERT INTO Person( PersonId, FirstName, LastName ) VALUES( 1, 'Homer', 'Simpson' );
INSERT INTO Person( PersonId, FirstName, LastName ) VALUES( 2, 'Lisa', 'Simpson' );
INSERT INTO Person( PersonId, FirstName, LastName ) VALUES( 3, 'Bart', 'Simpson' );

SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+
3 rows in set (0.00 sec)

Let’s run a REPLACE statement against that table and select its contents again:

REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 3, 'Sideshow', 'Bob' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Sideshow  | Bob      |
+----------+-----------+----------+
3 rows in set (0.00 sec)

We can see that Bart Simpson has been replaced with Sideshow Bob as specified. That’s because I used a PersonId of 3, which was Bart’s PersonId.

In this example I replaced all columns except for the primary key.

Missing Columns

If we don’t include a column in our list of columns to replace, then that column’s default value is inserted (just like when using the INSERT statement).

Example:

REPLACE INTO Person( PersonId, LastName ) VALUES( 2, 'Einstein' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | NULL      | Einstein |
|        3 | Sideshow  | Bob      |
+----------+-----------+----------+
3 rows in set (0.00 sec)

In this example I updated the LastName column but not the FirstName column. Therefore the FirstName column was set to its default value (in this case NULL).

Inserting New Rows

The REPLACE statement also inserts completely new rows if required – it’s not limited to replacing existing rows.

To insert a new row, the row must use a PRIMARY KEY or UNIQUE index that’s not already in the table.

Here’s an example of using a REPLACE statement to insert a new row:

REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 4, 'Ned', 'Flanders' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | NULL      | Einstein |
|        3 | Sideshow  | Bob      |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
4 rows in set (0.00 sec)

So we can see that table now contains 4 rows, the fourth being the one we just inserted. The reason it was inserted (and didn’t replace an existing row) is because the PersonId value (the primary key) wasn’t already in the table.

Using `REPLACE ... SELECT`

We can use a SELECT statement to specify the rows from an existing table to insert into the new table.

Suppose we create another table, called Person2:

CREATE TABLE Person2
(
    PersonId INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

We can use the REPLACE statement to populate this new table with data from the existing Person table.

Like this:

REPLACE INTO Person2 ( PersonId, FirstName, LastName ) SELECT * FROM Person;
SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | NULL      | Einstein |
|        3 | Sideshow  | Bob      |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
4 rows in set (0.00 sec)

So our new table resembles the previous one.

In this case, all rows were inserts. That’s because our new table didn’t contain any rows, and so there was nothing to replace. To demonstrate the full capability of the REPLACE statement, we should try to replace existing data in the new table.

So let’s change some data in the old table:

REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 2, 'Lisa', 'Einstein' );
REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 3, 'Bart', 'Simpson' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
4 rows in set (0.00 sec)

And let’s run the REPLACE ... SELECT statement again:

REPLACE INTO Person2 ( PersonId, FirstName, LastName ) SELECT * FROM Person;
SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
4 rows in set (0.00 sec)

The table now reflects the updated data from the old table.

Tables Without a `PRIMARY KEY` or `UNIQUE` Index

Using REPLACE on a table that doesn’t have a PRIMARY KEY or UNIQUE index will result in REPLACE behaving just like an INSERT statement. That’s because there’s no index to determine whether a new row duplicates another.

To demonstrate this, let’s drop the primary key from our second table:

ALTER TABLE Person2 DROP PRIMARY KEY;

Now let’s run the REPLACE statement again:

REPLACE INTO Person2 ( PersonId, FirstName, LastName ) SELECT * FROM Person;
SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
8 rows in set (0.00 sec)

Now we have a whole bunch of duplicate rows. That’s because we didn’t have a PRIMAY KEY or UNIQUE index to indicate to REPLACE which rows should be replaced.

The `TABLE` Statement

We can use the TABLE statement instead of the SELECT * statement if we want to move the whole table across to the new table.

Therefore we can replace the previous example with this:

REPLACE INTO Person2 ( PersonId, FirstName, LastName ) TABLE Person;
TABLE Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
12 rows in set (0.00 sec)

Of course, this resulted in four new rows being inserted into the table, due to it having no PRIMARY KEY or UNIQUE index. In this example I also used the TABLE statement to select all rows and columns from the table after I did the update.

The `SET` Clause

We can use the INSERT INTO ... SET syntax instead of the INSERT INTO ... VALUES syntax to achieve the same result. The INSERT INTO ... SET syntax is a MySQL extension to the SQL standard.

Example:

REPLACE INTO Person
SET PersonId = 2, LastName = 'Simpson';
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | NULL      | Simpson  |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
4 rows in set (0.00 sec)

More Information

See the MySQL documentation for more information about the REPLACE statement.

Example

Missing Columns

Inserting New Rows

Using REPLACE ... SELECT

Tables Without a PRIMARY KEY or UNIQUE Index

The TABLE Statement

The SET Clause

More Information

Using `REPLACE ... SELECT`

Tables Without a `PRIMARY KEY` or `UNIQUE` Index

The `TABLE` Statement

The `SET` Clause