In MySQL the REPLACE statement is normally used to replace existing rows in a table. But it’s not limited to just replacing existing rows. There are times when REPLACE will also insert new rows. It all depends on the incoming data, as well as how the table is structured in relation to the incoming data.

When we use the REPLACE function to replace a row in a table, we usually specify the row to replace, based on the PRIMARY KEY or UNIQUE index column.

Here are three cases where a new row will be inserted:

• If the table doesn’t have a PRIMARY KEY or UNIQUE index column, then the REPLACE statement will have no choice but to insert a new row.
• If we do specify a value for the PRIMARY KEY or UNIQUE index column, but that value doesn’t exist in the table, then a new row will be inserted.
• If the table does have a PRIMARY KEY or UNIQUE index column, and it’s an AUTO_INCREMENT column, but we don’t provide a value for that in our REPLACE statement.

Example of these below.

Example – No Primary Key Column

Suppose we create the following table (without a primary key column):

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    PersonId INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

This table doesn’t contain any data yet. It also doesn’t have a PRIMARY KEY or UNIQUE index column, so any time we run REPLACE it will insert a new row.

Lets’ try that:

REPLACE INTO Person( PersonId, FirstName, LastName ) 
VALUES( 1, 'Peter', 'Griffin' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Peter     | Griffin  |
+----------+-----------+----------+

So it inserted a row as expected.

Now, to demonstrate what can happen when the table doesn’t have a primary key column, let’s run the same code again:

REPLACE INTO Person( PersonId, FirstName, LastName ) 
VALUES( 1, 'Peter', 'Griffin' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Peter     | Griffin  |
|        1 | Peter     | Griffin  |
+----------+-----------+----------+

The row was inserted again, even though we used the same PersonId that’s in the previous row. It was inserted because we don’t have a primary key constraint against the PersonId column.

Example – Specifying a New Value for the Primary Key Column

Let’s create another table, this time with a primary key, and then use REPLACE to insert data:

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

REPLACE INTO Person2( PersonId, FirstName, LastName ) 
VALUES( 1, 'Peter', 'Griffin' );

SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Peter     | Griffin  |
+----------+-----------+----------+

Now let’s insert another row:

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

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Peter     | Griffin  |
|        2 | Ned       | Flanders |
+----------+-----------+----------+

The row was inserted because the value I provided for the primary key column isn’t already in the table.

If I provide a value that’s already in the database, then that row will be replaced (instead of a new row being inserted).

Here’s an example of what I mean:

REPLACE INTO Person2( PersonId, FirstName, LastName ) 
VALUES( 2, 'Homer', 'Simpson' );
SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Peter     | Griffin  |
|        2 | Homer     | Simpson  |
+----------+-----------+----------+

Example – An AUTO_INCREMENT Column

If the PRIMARY KEY or UNIQUE index column is an AUTO_INCREMENT column, then omitting this column from our REPLACE statement will result in a new row being inserted.

Let’s create a table with an AUTO_INCREMENT column and insert data:

DROP TABLE IF EXISTS Person3;
CREATE TABLE Person3
(
    PersonId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

REPLACE INTO Person3( FirstName, LastName ) VALUES( 'Peter', 'Griffin' );
REPLACE INTO Person3( FirstName, LastName ) VALUES( 'Ned', 'Flanders' );

SELECT * FROM Person3;

And let’s insert data:

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Peter     | Griffin  |
|        2 | Ned       | Flanders |
+----------+-----------+----------+

We can see that the rows were inserted, even though I didn’t provide a value for the PRIMARY KEY / AUTO_INCREMENT column.

But to be sure, we can replace any of those existing rows by referencing their primary key value:

REPLACE INTO Person3( PersonId, FirstName, LastName ) 
VALUES( 1, 'Homer', 'Simpson' );

SELECT * FROM Person3;

And let’s insert data:

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Ned       | Flanders |
+----------+-----------+----------+

So omitting a value for the AUTO_INCREMENT column results in the row being inserted, while specifying a value for that column results in the row being replaced (assuming that there’s already a row with that value in the table).

Here’s what happens when we do specify the value, but there isn’t a row with that value in the table:

REPLACE INTO Person3( PersonId, FirstName, LastName ) 
VALUES( 3, 'Bart', 'Simpson' );

SELECT * FROM Person3;

Result:

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

A new row is inserted as expected.