How to insert in one table from another table by using Join that eliminates duplicates

Question

I created a table A and B with same exact columns:

create or replace table a (
    a1 varchar(30),
    a2 int,
    a3 int
);

create or replace table b (
    b1 varchar(30),
    b2 int,
    b3 int
);

And then inserted 2 values in each:

insert into a values ('abc', 1, 2);
insert into a values ('abd', 1, 2);

insert into b values ('abd', 1, 2);
insert into b values ('abe', 1, 2);

How to make insert statement so that it inserts only records from B that do NOT exist in table A (by e.g. using join statement?)?

insert into table a (
    select * from b
    );

(without Primary Key help). Bonus point is to check only on 2 columns if they are the same or not (e.g. a1 != b1 and a2 != b2).

Thanks!

To clarify: in the example given, you'd want the row `'abe', 1, 2` inserted into table `a`? — Simon Brahan, Jul 10 '18 at 12:46

score 2 · Answer 1 · answered Jul 10 '18 at 12:52

2

This should give you what you need:

insert into a 
select b.*
from b left join  a on a.a1 = b.b1 and a.a2 = b.b2
where a.a1 is null

answered Jul 10 '18 at 12:52

Rigerta

score 1 · Answer 2 · answered Jul 10 '18 at 12:46

1

Try this

insert int a
select * from b
where (b1, b2, b3) not in (select a1, a2, a3 from a)

answered Jul 10 '18 at 12:46

Gaj

score 1 · Accepted Answer · answered Jul 10 '18 at 13:29

1

I would use not exists :

insert into a (a1, a2, a3)
     select b1, b2, b3
     from b 
     where not exists (select 1 from a where a1 = b.b1 and a2 = b.b2);

answered Jul 10 '18 at 13:29

Yogesh Sharma

3 Answers3