Merging two tables where unique key comprises multiple columns

Question

I have two tables A and B (with the same schema), and I want to merge them by inserting all entries from A into B. If table B already has data associated with a key from A, I want to silently drop those data.

The problem is that table B has a unique key index that consists of three columns, so I can't just say "WHERE A.key <> B.key".

I can't seem to formulate an SQL statement along the lines of:

INSERT INTO B 
VALUES ((SELECT * FROM A WHERE ... NOT IN ...))

Is there a way to INSERT those rows from A into B where the corresponding three-column-key doesn't exist in B yet?

please let me know the columns of those two tables – Chella Nov 22 '12 at 11:20 — Chella, Nov 22 '12 at 11:20
@Chella: do you need the names or the respective types? – Philip Nov 22 '12 at 11:23 — Philip, Nov 22 '12 at 11:23

score 2 · Accepted Answer · answered Nov 22 '12 at 11:24

INSERT INTO B 
(Col1, Col2, Col3, ColN)
SELECT
A.Col1, A.Col2, A.Col3, COlN
FROM A
LEFT JOIN B
ON A.COL1 = B.Col1
AND A.COL2 = B.Col2
AND A.COL3 = B.Col3
WHERE B.Col1 IS NULL

Essentially Join the 2 tables with a left join, and insert all from A where B is null (No corresponding value in B table for a Join on the 3 Key columns)

score 1 · Answer 2 · edited May 23 '17 at 11:43

You could use NOT EXISTS instead of NOT IN

INSERT B
SELECT  *
FROM    A
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    B
            WHERE   A.Key1 = B.Key1
            AND     A.Key2 = B.Key2
        )

Although according to this MySQL optimises LEFT JOIN/IS NULL better than not exists:

INSERT B
SELECT  A.*
        LEFT JOIN B
            ON A.Key1 = B.Key1
            AND A.Key2 = B.Key2
WHERE   B.Key1 IS NULL

Merging two tables where unique key comprises multiple columns

2 Answers2