When we use the REPLACE statement in MySQL, we have the option of replacing the values of all columns in the row or just certain columns.

If we choose to replace just certain columns, we need to be aware of how this will affect the outcome. The “missing columns” in the title of this article refers to when we specify a value for just some, but not all columns.

It might be tempting to assume that by omitting a column from our REPLACE statement it will leave that column alone (at its previous value). But that’s not the case. Any columns we omit from our REPLACE statement will be set to their default value. The REPLACE statement actually deletes the row and then inserts it with the new values, so any existing values will be long gone.

However if we use an AUTO_INCREMENT column for the primary key then omitting that column will cause the column to be set to the next incremental value.

Example of Omitting a Column from REPLACE

Suppose we create the following table:

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

INSERT INTO Person( FirstName, LastName ) VALUES( 'Homer', 'Simpson' );
INSERT INTO Person( FirstName, LastName ) VALUES( 'Lisa', 'Simpson' );
INSERT INTO Person( FirstName, LastName ) VALUES( 'Bart', 'Simpson' );

SELECT * FROM Person;

Result:

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

And we run the REPLACE statement against it, like this:

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

Result:

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

We can see that the second row was replaced because we specified a PersonId of 2.

But we didn’t specify a value for the FirstName column – we only specified the LastName.

When we omit a column like this, the default value is inserted for that column. In this case the default value is NULL, mainly because we didn’t specify a default value when we created the table.

Let’s add a DEFAULT constraint to the column:

ALTER TABLE Person 
MODIFY COLUMN FirstName VARCHAR(20) DEFAULT "Hey You!";

And run the REPLACE statement again:

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

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Hey You!  | Einstein |
|        3 | Bart      | Simpson  |
+----------+-----------+----------+

Once again I omitted the FirstName column from the statement, and once again it inserted the default value. But this time the default value was Hey You!.

Auto Increment Columns

In our table, the PersonId column (the primary key) uses AUTO_INCREMENT to automatically increment the value each time a new row is inserted. When we use REPLACE to replace an existing row, we specify the value of that row’s PersonId.

However, if we omit this value, then REPLACE has no option but to insert a new row:

REPLACE INTO Person( LastName ) VALUES( 'Smithers' );
SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Hey You!  | Einstein |
|        3 | Bart      | Simpson  |
|        4 | Hey You!  | Smithers |
+----------+-----------+----------+

So it basically acts just like INSERT when we omit the value for the primary key column in this case.