Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy rows from one table to another, ignoring duplicates

Tags:

sql

tsql

I have 2 tables (srcTable1 & destTable) that have identical schemas. I am trying to copy all rows from srcTable to destTable and ignore the duplicates. I thought I could just add a WHERE clause with a subquery that would give me only the rows that aren't duplicates. However, it doesn't seem to work. I don't get any rows inserted or selected.

INSERT INTO destTable SELECT * FROM srcTable WHERE NOT EXISTS(SELECT * FROM destTable) 

I realize I could do something like this:

INSERT INTO destTable SELECT * FROM srcTable WHERE MyKey IN (SELECT MyKey FROM destTable) 

However, my tables have multiple keys and I can't think of how you could do this with multiple keys.

Any idea what I'm doing wrong or do you have any better ideas?

like image 569
Jeff Stock Avatar asked Mar 17 '09 21:03

Jeff Stock


People also ask

How do I ignore duplicate rows in SQL?

Following is the syntax: select count(distinct yourColumnName) from yourTableName; In MySQL, COUNT() will display the number of rows. DISTINCT is used to ignore duplicate rows and get the count of only unique rows.

How do you avoid inserting duplicate records in a table?

Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.


1 Answers

Your problem is that you need another where clause in the subquery that identifies what makes a duplicate:

INSERT INTO destTable SELECT Field1,Field2,Field3,...  FROM srcTable WHERE NOT EXISTS(SELECT *                   FROM destTable                   WHERE (srcTable.Field1=destTable.Field1 and                        SrcTable.Field2=DestTable.Field2...etc.)                  ) 

As noted by another answerer, an outer join is probably a more concise approach. My above example was just an attempt to explain using your current query to be more understandible. Either approach could technically work.

INSERT INTO destTable SELECT s.field1,s.field2,s.field3,...  FROM srcTable s         LEFT JOIN destTable d ON (d.Key1 = s.Key1 AND d.Key2 = s.Key2 AND...) WHERE d.Key1 IS NULL 

Both of the above approaches assume you are woried about inserting rows from source that might already be in destination. If you are instead concerned about the possibility that source has duplicate rows you should try something like.

INSERT INTO destTable SELECT Distinct field1,field2,field3,...  FROM srcTable   

One more thing. I'd also suggest listing the specific fields on your insert statement instead of using SELECT *.

like image 184
JohnFx Avatar answered Sep 18 '22 20:09

JohnFx