In MySQL we can use the REPLACE statement to replace data in an existing table without inserting a new row. When we do this, we have a choice of syntax when it comes to selecting the row to replace.

One option (and probably the most common option) is to use the VALUES clause. Another option is to use the SET clause.

Below is an example of using the SET clause when using MySQL’s REPLACE statement.

Example

Suppose we have a table like this:

SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+

Here’s an example of using the REPLACE statement with the SET clause to replace one of the rows in that table:

REPLACE INTO Person
SET PersonId = 2, FirstName = 'Lisa', LastName = 'Einstein';
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+

Here we replaced row 2 with the new data.

The above example is the equivalent of the following:

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

Inserting a New Row

Omitting the primary key or UNIQUE index column results in a new row being inserted:

REPLACE INTO Person
SET FirstName = 'Lisa', LastName = 'Einstein';
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Lisa      | Einstein |
+----------+-----------+----------+

The same applies when we specify a primary key value that doesn’t exist:

REPLACE INTO Person
SET PersonId = 5, FirstName = 'Lisa', LastName = 'Einstein';
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Lisa      | Einstein |
|        5 | Lisa      | Einstein |
+----------+-----------+----------+