CODEWITHSUNDEEP
mysqlsql
user2118839
user2118839

Reputation: 63

Merge 2 mysql tables with the same rows

I have 2 mysql tables with the same rows, on both the tables some information is missing and now I need to merge the two tables into one table with the complete information.

This is how the tables look like:

Table1:

Name | Adres       | Postal
------------------------------
Koen | Stationsweg | 
     | Marktplein  | 4342FG

Table 2:

Name | Adres       | Postal
------------------------------
     | Stationsweg | 4368RT
Bert | Marktplein  |

The final table needs to look like:

Name | Adres       | Postal
------------------------------
Koen | Stationsweg | 4368RT
Bert | Marktplein  | 4342FG

Upvotes: 2

Views: 119

Answers (1)

John Woo
John Woo

Reputation: 263693

SELECT  MAX(COALESCE(a.Name, b.name)) name,
        a.adres,
        MAX(COALESCE(a.postal, b.postal)) postal
FROM    table1 a
        LEFT JOIN table2 b
            ON a.adres = b.adres
GROUP   BY a.adres

if you want the result of the above query to be inserted let's say on another table: table3, Use INSERT INTO...SELECT

INSERT  INTO table3 (name, adres, postal)
SELECT  MAX(COALESCE(a.Name, b.name)) name,
        a.adres,
        MAX(COALESCE(a.postal, b.postal)) postal
FROM    table1 a
        LEFT JOIN table2 b
            ON a.adres = b.adres
GROUP   BY a.adres

Upvotes: 2

Related Questions