Search code examples
phpmysqlmergemerging-data

MySQL merge tables with different structure

I have two databases with different structure.

Table 1:

ch_code    ch_def    ch_weight

Table 2:

address    ch_code

I need to merge this two tables, so the structure would look like:

ch_code    ch_def    ch_weight    address

The number or rows in two tables are different (table 1 has more data).

Should I use merge, union.. something else?

Thank you!

Solution

  • Here's a solution which should handle the three possible cases:

    1. There's a ch_code value in t1 that's not in t2
    2. There's a ch_code value in t2 that's not in t1
    3. There's a ch_code value in both t1 and t2

    SELECT t1.ch_code, t1.ch_def, t1.ch_weight, '' as address from t1 where not exists (select * from t2 where t2.ch_code = t1.ch_code)

    UNION

    SELECT t2.ch_code, '' as ch_def, '' as ch_weight, t2.address from t2 where not exists (select * from t1 where t1.ch_code = t2.ch_code)

    UNION

    SELECT t1.ch_code, t1.ch_def, t1.ch_weight, t2.ch.address from t1 left join t2 on t1.ch_code = t2.ch_code

    Once you've obtained that resultset then you may do your INSERT INTO if you have a new table for housing the merged data.