CODEWITHSUNDEEP
mysqljoinmerge
emax
emax

Reputation: 7245

mysql - how to merge two tables based on a column?

I have two tables, table1 and table2

table1
    id |    val    |
    a  |           |
    a  |           |
    b  |           |
    b  |           |

table2
    id |    val    |
    a  |    2      |
    a  |    3      |
    b  |    1      |
    b  |    0      |
    c  |    5      |        
    d  |    2      |        
    d  |    -2     |

I want to fill up the values val of table1 based on the value of table2 in order to get:

table1
    id |    val    |
    a  |    2      |
    a  |    3      |
    b  |    1      |
    b  |    0      |

This what I am doing

SELECT *
FROM table1 JOIN table2 ON table1.id = table2.id;

but what I am getting is a table with four columns repeating each values twice.

Upvotes: 0

Views: 1599

Answers (2)

SIDU
SIDU

Reputation: 2278

If val is nullable:

SELECT DISTINCT tab1.id, ifnull(tab1.val, tab2.val) AS val
FROM tab1
JOIN tab2 ON tab1.id = tab2.id

If val is not null:

SELECT DISTINCT tab1.id, if(tab1.val = '', tab2.val, tab1.val) AS val
FROM tab1
JOIN tab2 ON tab1.id = tab2.id

enter image description here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

This is the only interpretation of your query which makes sense to me. We can retain records from table2 depending on whether or not their id values be present in table1:

SELECT t2.id, t2.val
FROM table2 t2
INNER JOIN
(
    SELECT DISTINCT id
    FROM table1
) t1
    ON t2.id = t1.id

Upvotes: 0

Related Questions