Merge two or more tables in phpMyAdmin
Today, we’ll discuss how to merge two or more tables in phpMyAdmin. Sometimes we may need to merge multiple tables that have the same structure. So here, we explain to you a simple way to merge multiple tables in phpMyAdmin.
Checkout more articles on PHP
Example
Let’s take an example where we will have three tables named customers1
, customers2
and customers3
. We will merge all those tables and create a final table named customers
.
For demo purposes, we are taking 3 records in each table but it helps to merge large records of the multiple tables as well.
All three tables contain the sample data as shown below in the images.
Steps to merge two or more tables
- Create a final table structure
- Copy records from sub tables to final table
- Update primary key if exist
1. Create a final table structure
First of all, we have to create a final table where we can merge all the sub table records. So follow one of the methods to create a final table structure.
Method 1:
Go to table customers1 > Operations > Copy table to (database.table)
Now write a final table name as customers
and select the “Structure only” option then click on the “Go” button. Your selection should look like below.
Method 2:
In this alternative method, you can create a table with structure by executing the SQL query. Run the following SQL query.
1 | create table customers like customers1; |
When you click on the Go button or execute your query then the table named customers
will be created.
2. Copy records from sub tables to final table
Now, you need to run the below query in phpMyAdmin to copy records from the customers1
to customers
table.
1 | INSERT INTO customers SELECT * FROM customers1; |
The same way, we will run the following queries to copy the records of the tables customers2
and customers3
.
1 2 3 4 5 | /* Copy records from `customers2` to `customers` table */ INSERT INTO customers SELECT * FROM customers2; /* Copy records from `customers3` to `customers` table */ INSERT INTO customers SELECT * FROM customers3; |
That’s it to merge multiple tables.
3. Update primary key if exist
If we have used the primary key in tables and when we run the above queries then it will throw an error due to duplicate value for the primary key as shown in the below image.
To avoid this error, we need to do one more step before inserting the data.
First, we have to check the last value of the field which has assigned the primary key. After copying the table customers1
, we will get the last value 3
of the customer_id
field from the customers
table.
Now, we have to update the primary key values of the customers2
table. Run the following query.
1 | UPDATE customers2 SET `customer_id` = 3 + `customer_id`; |
After running the above query you can see the customers2
table which has reordered the customer_id
field starting from 4 as below image.
Now we can easily merge the customers2
table into the customers
table by running the following query.
1 | INSERT INTO customers SELECT * FROM customers2; |
The same way, we’ll run the following queries to update and merge the customers3
table.
1 2 3 | UPDATE customers3 SET `customer_id` = 6 + `customer_id`; INSERT INTO customers SELECT * FROM customers3; |
Finally, we will have all records in table customers
as shown in the below image.
That’s it for today.
Thank you for reading. Happy Coding..!!