CODEWITHSUNDEEP
mysqlsql
zenCoder
zenCoder

Reputation: 750

Add values from Table1 into Table2 without adding duplicates

[MySQL 5.5]

I have two tables - Table_1 and Table_2.

They have identical columns - Col1, Col2, Col3, Col4.

Table_1 can have duplicates on columns Col1 and Col2.

Example-1:

   Col1,Col2,Col3,Col4
1) a   ,b   ,c   ,1      
2) a   ,b   ,d   ,2

Now Table_2 has the following rows:

Example-2:

   Col1,Col2,Col3,Col4
1) a   ,b   ,e   ,1      
2) a   ,c   ,f   ,2

I want to write all rows from Table_2 into Table_1 that do not have duplicates on Col1 and Col2. In the above instance, the insert should ignore row 1 in Example-2 above and add row 2 since there are no duplicates for combination (a,c) in Table_1.

Adding Unique keys on Col1 and Col2 will not work as it will delete row no 2 in Example 1.

Both Table_1 and Table_2 have 2 million rows each. Nested select statements(which I tried) have spelled disaster in terms of execution time.

Is there another way out of this?

Upvotes: 1

Views: 174

Answers (4)

AgRizzo
AgRizzo

Reputation: 5271

Find the rows that don't exist via LEFT JOIN and NULL checking in the WHERE clause

INSERT INTO Table_1 (Col1, Col2, Col3, Col4)
SELECT Table_2.Col1, Table_2.Col2, Table_2.Col3, Table_2.Col4
FROM Table_2
LEFT JOIN Table_1
  ON Table_2.Col1 = Table_1.Col1
    AND Table_2.Col2 = Table_1.Col2
WHERE Table_2.Col1 IS NULL;

Upvotes: 0

Adarsh Shah
Adarsh Shah

Reputation: 6775

See below. Its using join so will have better performance.

INSERT INTO Table_1
SELECT T2.Col1
    ,T2.Col2 
    ,T2.Col3
    ,T2.Col4
FROM Table_2 T2
LEFT JOIN Table_1 T1
    ON T2.Col1 = T1.Col1
    AND T2.Col2 = T1.Col2
WHERE T1.Col1 IS NULL
    AND T1.Col2 IS NULL

Upvotes: 0

John Chrysostom
John Chrysostom

Reputation: 3983

insert into table1
select *
from table2
where concat(Col1,Col2) not in
(
    select concat(col1,col2)
    from table1
) as T

Upvotes: 0

Lamak
Lamak

Reputation: 70658

This should do:

INSERT INTO Table_1
SELECT *
FROM Table_2 A
WHERE NOT EXISTS(SELECT 1 FROM Table_1
                 WHERE Col1 = A.Col1
                 AND Col2 = A.Col2)

Upvotes: 1

Related Questions