There are one of two things you might be looking for – merging two MySQL tables, or the MERGE TABLE syntax – this article explains both, with examples.
Merging Tables in MySQL (Moving Data From One Table Into Another)
A note before I get to the code – if you’re moving data from one table to another, you should take into account the contents of the data and ensure that it will still be valid when merged with the second data set. This is particularly important if you’re using the primary key of the table(s) in a relationship with another table – depending on which merge method you use, primary keys may change, which might break relationships.
As usual, the best way to mitigate any issue is to do a backup before making any changes to your data.
All of the below examples will use two tables called table1 and table2.
Method 1: Inserting All Records From One Table Into Another (Non-Duplicated Data)
This method will copy all records from table2 into table1. If an auto-incrementing numeric primary key is being used in the tables, it will need to be omitted to avoid conflicts – so the primary key for the records in table2 will be changed in the merged table.
INSERT INTO table1 (all, columns, except, primary_key) SELECT all, columns, except, primary_key FROM table2;
Above, all, columns, except, primary_key is just an example list of column names.
If you are using UUIDs that can’t conflict, there’s no need to exclude them from the MySQL statement above.
Method 2: Insert All Records From One Table To Another, Ignoring Duplicates
With this method, rows in table1 will supersede those in table2 if they have a matching primary key. That is to say, rows that do not exist in table1 from table2 will be copied from table2 to table1.
INSERT IGNORE INTO table1 SELECT * FROM table2;
Method 3: Insert All Records From One Table to Another, Replacing Duplicates
On the other hand, this method will overwrite rows in table1 if there is a corresponding row in table2 with a matching primary key. New rows from table2 will also be copied to table1.
REPLACE INTO table1 SELECT * FROM table2;
Querying Two Tables As One
The UNION operator can be used to merge two similar result sets from two queried tables.
SELECT * FROM tabel1 UNION SELECT * FROM tabel2;
Above, the results will include all records from both table1 and table2.
The UNION operator can only merge results from tables/queries containing the same number of columns containing similar data.
MERGE Syntax (Creating a Virtual Table Which Contains Data from Several Tables)
There is a final, advanced method for using the MyISAM database engine (you probably are, it’s the default option in MySQL and MariaDB).
MERGE TABLE will allow you to create a table that, when queried, returns results from several other tables. Kind of like a virtual table – it doesn’t contain any data; it pulls it from the other tables but can be queried as if it were a regular table.
This is best illustrated by viewing the MySQL commands used for creating the merged table using MERGE TABLE.
This example follows the example in the MySQL documentation, with table1 and table2 being used to create a merged table3:
CREATE TABLE table1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message CHAR(20)) ENGINE=MyISAM;
CREATE TABLE table2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message CHAR(20)) ENGINE=MyISAM;
INSERT INTO table1 (message) VALUES ('Testing'),('table'),('table1');
INSERT INTO table2 (message) VALUES ('Testing'),('table'),('table2');
CREATE TABLE table3 (
id INT NOT NULL AUTO_INCREMENT,
message CHAR(20), INDEX(id))
ENGINE=MERGE UNION=(table1,table2) INSERT_METHOD=LAST;
The above example creates table1 and table2 with the MyISAM database engine and two columns (id, which is the primary key and message).
Test data is then inserted into each table.
table3 is then created – the id table is indexed as the primary key in the first two tables, but not for the third MERGE table – there it is indexed but does not act as a PRIMARY KEY as it cannot be UNIQUE given that the same key could exist at the same time in both of the tables being merged.
Now, table3 can be queried – it will return the results from table1 and table2 combined.
SELECT * FROM table3;
+---+---------+ | id | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | table1 | | 1 | Testing | | 2 | table | | 3 | table2 | +---+---------+
Modifying a MERGE table is as simple as dropping the table and recreating it with the tables you wish to see MERGEd.
For more information on this, check out the official docs:
https://dev.mysql.com/doc/refman/8.0/en/merge-storage-engine.html
