The MySQL REPLACE statement allows us to replace data in a table with new data. The new data can be explicitly provided in the statement or it could come from the result of a query. Therefore, we can use the REPLACE statement to effectively “refresh” a table with new/updated data from another table.

Example

Suppose we create two tables like this:

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

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

Let’s insert data into the first table:

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  |
+----------+-----------+----------+

Now let’s use the REPLACE statement to copy all those rows into the second table:

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

SELECT * FROM Person2;

Result:

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

We can see that second table (Person2) now contains exactly the same data that the first table (Person) contains.

Now let’s update data in the first table:

REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 2, 'Lisa', 'Einstein' );
REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 3, 'Sideshow', 'Bob' );
REPLACE INTO Person( PersonId, FirstName, LastName ) VALUES( 4, 'Ned', 'Flanders' );

SELECT * FROM Person;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Sideshow  | Bob      |
|        4 | Ned       | Flanders |
+----------+-----------+----------+

Now let’s refresh the second table with the contents of the first table:

REPLACE INTO Person2 ( PersonId, FirstName, LastName ) 

SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Sideshow  | Bob      |
|        4 | Ned       | Flanders |
+----------+-----------+----------+

We used exactly the same REPLACE statement that we used before to populate the second table.

We can see that it has updated multiple rows with the latest data, and it has even inserted a new row where applicable.

The TABLE Statement

MySQL also has the TABLE statement, which can be used as an alternative to the above SELECT statement. The TABLE statement provides a more concise way to return all rows and columns from a table.

Therefore we can change the above statement to the following:

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

SELECT * FROM Person2;

Result:

+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Sideshow  | Bob      |
|        4 | Ned       | Flanders |
+----------+-----------+----------+

In this case nothing changed because we haven’t updated the first table since the previous example. However, the TABLE statement does provide a more concise alternative to the SELECT * option.

One thing to be mindful of though, is that the TABLE statement doesn’t include invisible columns in its result set. If the source table has any invisible columns, and you need them to be included in the refresh to the second table, use a SELECT statement that explicitly lists the invisible columns. Using the wildcard asterisk (*) is not sufficient to include invisible columns. However, you could use the wildcard asterisk (*) to include all visible columns, but also explicitly specify any invisible columns. See How to Select Data from an Invisible Column in MySQL for more information.

Dealing with Deleted Rows

While the REPLACE statement can be handy for refreshing data in a table, it’s not so handy when it comes to deleted rows in the first table.

Here’s one option for handling deleted rows:

DELETE FROM Person2 
WHERE PersonId NOT IN (SELECT PersonId FROM Person);
SELECT * FROM Person2;

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

That deletes any rows in the second table that aren’t present in the first table (based on the primary key column in the second table not being present in the first table). Then the REPLACE statement is run to refresh any remaining rows in the second table.