CODEWITHSUNDEEP
sql
Matei Zoc
Matei Zoc

Reputation: 3919

sql - how to insert on table1 from table2 but dont insert the duplicate value

i have 2 table, table1 and table2, each of them have rows added table1 = 2m rows and table2 = 1m rows and some rows from table2 is already on table1, and table1 have unique value

i try this command:

INSERT INTO table1 (top1, top2, top3)
  SELECT top1, top2, top3 FROM table2

top1 is unique value, how i can make the selection so if table2 top1 is already on table1 top1 to pass next?

Upvotes: 0

Views: 52

Answers (1)

sgeddes
sgeddes

Reputation: 62841

There are several ways to do this, and some different databases provide easier methods. Here's a generic solution with not exists:

INSERT INTO table1 (top1, top2, top3)
SELECT top1, top2, top3 FROM table2 t2
WHERE NOT EXISTS (
    select 1 
    from table1 t1 
    where t1.top1 = t2.top1)

Another generic option with an outer join \ null check:

insert into table1 (top1, top2, top3)
select t2.top1, t2.top2, t2.top3
from table2 t2 
   left join table1 t1 on t2.top1 = t1.top1
where t1.top1 is null

Upvotes: 2

Related Questions