When using the MySQL REPLACE statement to update another table based on a source table, we have the option of using a SELECT statement to select the whole table (or part of it) or the TABLE statement to select the whole table.

Below is an example of refreshing a table from another one using the TABLE statement with the REPLACE statement.

Example

Suppose we select all rows and columns from the following tables:

SELECT * FROM Person;
SELECT * FROM Person2;

Result:

mysql> SELECT * FROM Person;
+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Simpson  |
|        3 | Bart      | Simpson  |
|        4 | Ned       | Flanders |
+----------+-----------+----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Person2;
+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Homer     | Simpson  |
|        2 | Lisa      | Einstein |
|        3 | Sideshow  | Bob      |
+----------+-----------+----------+
3 rows in set (0.00 sec)

We can see that there’s a discrepancy in the data between the two tables.

Let’s refresh the second table so that it contains the same data as the first table:

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

Result:

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

Now the second table contains the same data as the first table.

This is the same result that we would’ve got if we’d used the following code:

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

Invisible Columns

One thing to be mindful of when using the above technique, 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, you’ll need to use a SELECT statement that explicitly lists the invisible columns.

When doing this, using the wildcard asterisk (*) is not sufficient to include invisible columns. Any invisible columns must be explicitly referenced. 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.