How to merge these two mysql tables?

tableone

a  |b| c
--------
a1 | |c1
a2 | |c2
a3 | |c3

tabletwo

a | b
-----
a1|b1
a2|b2
a3|b3
a4|b4
a5|b5

merged

a | b | c
a1| b1|c1
a2| b2|c2
a3| b3|c3
a4| b4|
a5| b5|

So I have the two table and I want to m开发者_运维知识库erge them. I tried in phpmyadmin these two options:

INSERT IGNORE INTO tableone SELECT * FROM tabletwo;
REPLACE INTO tableone SELECT * FROM tabletwo;

But both do the same (put one table on another), but I want to merge them. So what query must I use? I hope you understand the the question...

Try these queries back-to-back:

INSERT IGNORE INTO tableone (a) SELECT a FROM tabletwo;

UPDATE tableone, tabletwo
SET tableone.b = tabletwo.b
WHERE tableone.a = tabletwo.a;

To answer pst, something like this, assuming that tableone does not have a b column:

INSERT INTO tablethree (a, b, c)

SELECT * FROM ((
    SELECT
        tableone.a AS a,
        tabletwo.b AS b,
        tableone.c AS c

    FROM tableone

    LEFT OUTER JOIN tabletwo
    ON tableone.a = tabletwo.a
) UNION (
    SELECT
        tabletwo.a AS a,
        tabletwo.b AS b,
        tableone.c AS c

    FROM tableone

    RIGHT OUTER JOIN tabletwo
    ON tableone.a = tabletwo.a
)) AS x

This is untested, but should work. ("Should"...) The UNION is a workaround for MySQL's lack of FULL OUTER JOIN.

CREATE TABLE merged AS
SELECT A.a,A.b,B,B.c FROM
tabletwo A LEFT JOIN tableone USING (a);

I chose tabletwo first because it has all keys needed

Of course, since tabletwo has all the keys, that's the perfect case.

Using set theory, what if the intersection of the keys from tableone and the keys from tabletwo is not the same as all the keys of tableone or all the keys of tabletwo, what do you do ??? Do this:

CREATE TABLE merged AS
SELECT
    A.a,B.b,C.c
FROM
(
    SELECT a FROM tableone
    UNION
    SELECT a FROM tabletwo
) A
LEFT JOIN tableone B USING (a)
LEFT JOIN tabletwo C USING (a);

The inline SELECT A has all merged keys as a set UNION.

Any row from B missing something from C gets a NULL column for that missing entry from C.

Any row from C missing something from B gets a NULL column for that missing entry from B.