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.

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.

Tables - Clue Mediator
Tables – Clue Mediator

All three tables contain the sample data as shown below in the images.

customers1 - Clue Mediator
customers1 – Clue Mediator
customers2 - Clue Mediator
customers2 – Clue Mediator
customers3 - Clue Mediator
customers3 – Clue Mediator

Steps to merge two or more tables

  1. Create a final table structure
  2. Copy records from sub tables to final table
  3. 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.

Copy table to - Clue Mediator
Copy table to – Clue Mediator

Method 2:

In this alternative method, you can create a table with structure by executing the SQL query. Run the following SQL query.

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.

The same way, we will run the following queries to copy the records of the tables customers2 and 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.

Duplicate entry for primary key - Clue Mediator
Duplicate entry for primary key – Clue Mediator

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.

After running the above query you can see the customers2 table which has reordered the customer_id field starting from 4 as below image.

Updated customers2 - Clue Mediator
Updated customers2 – Clue Mediator

Now we can easily merge the customers2 table into the customers table by running the following query.

The same way, we’ll run the following queries to update and merge the customers3 table.

Finally, we will have all records in table customers as shown in the below image.

customers - Clue Mediator
customers – Clue Mediator

That’s it for today.
Thank you for reading. Happy Coding..!!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *