Nav Nav - 6 months ago 12
SQL Question

Insert rows from one table to another but only those rows that have no duplicates

I've seen this, this, this, this and this but my question is different.

I have a Table1:

id c a b rc bid
1 12 4 6 35 4
2 12 4 6 67 7
3 12 4 6 88 8
4 23 4 7 49 3
5 23 5 8 59 8


Table2 also has the same columns but does not have
bid
column.

A row is considered a duplicate if it has the same values of columns
c
,
a
and
b
. So rows 1, 2 and 3 are considered duplicates because they have 12, 4 and 6.

I want to insert rows of
Table1
to
Table2
, but only those rows that are not duplicates. Which means that rows 1, 2 and 3 won't get inserted to
Table2
. Only rows 4 and 5 will get inserted because they have no duplicates.

So Table2 will look like this after the inserts:

id c a b rc
1 23 4 7 49
2 23 5 8 59


I know I can get which rows have no duplicates using this query:

select distinct c,a,b,count(*) from Table1 group by c,a,b having count(*) > 1


But am not able to figure out how to insert these to
Table2
because the insertion requires specific columns to be specified.

Tried something like this which obviously doesn't work:

insert into Table2 (c, a, b, rc) select distinct c,a,b,count(*) from Table1 group by c,a,b having count(*) > 1

Answer

There are many ways to do that. You have already got so many correct answers. Here, I am giving the query based on the way you approached.

INSERT INTO Table2 (c, a, b, rc) 
SELECT
    c,
    a,
    b,
    count(*)
FROM
    Table1
GROUP BY c, a, b
HAVING  count(*) = 1;