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.