Skip to content
Advertisement

How to take union and write into the same table?

I have a table A with columns (x, y, z) and I have another table B with columns (x, y, z), basically the same schema.

I want to add those rows from B into A, which do not already exist in A, basically after the operation the union of A and B exist in table A.

How do I do that in sql?

I mean I know how to take a union, I was thinking something like :

INSERT OVERWRITE TABLE A
(SELECT a, b, c FROM A 
union 
SELECT a, b, c FROM B)

but this doesn’t seem right as I am selecting from the same table I’m writing into.

Advertisement

Answer

I want to add those rows from B into A, which do not already exist in A.

The idea is to filter before inserting into the table:

INSERT INTO TABLE A (a, b, c)
    SELECT a, b, c
    FROM B
    WHERE NOT EXISTS (SELECT 1
                      FROM A
                      WHERE B.a = A.a AND B.b = A.b AND B.c = A.c
                     );

Note: This does not filter out duplicate NULL values, but that can easily be handled as well (it just complicates the logic a bit).

You can use UNION if you want to create a new table with the combined rows.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement