How to Copy Rows Between Tables Without Creating Duplicates?

Copying Rows Between Tables Without Duplicating Data

When transferring data from one table to another, it's crucial to prevent duplicate rows from being inserted. To achieve this, we need a method that filters out existing records.

In your initial approach, you attempted to use a WHERE clause with a subquery to identify unique rows. However, the subquery lacked criteria for determining duplicates.

Solution Using Subquery with WHERE Filtering

To effectively filter out duplicates, you can modify your subquery as follows:

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.)
                 )

This updated subquery adds a condition that compares specific fields between the source and destination tables to determine if a duplicate exists.

Solution Using Outer Join

Alternatively, you can use an outer join to achieve the same result. An outer join retrieves all rows from one table and matching rows from another table, leaving unmatched rows as NULL.

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

In this case, the outer join matches rows based on their key fields (Key1, Key2, etc.). Rows in the source table that don't match any rows in the destination table will have NULL values for the key fields, and these rows will be inserted into the destination table.

Additional Considerations

If you're solely concerned about preventing duplicates from the source table, you can use a distinct keyword in your SELECT statement:

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

Lastly, specifying the specific fields in your INSERT statement instead of using SELECT * is recommended for performance reasons.

The above is the detailed content of How to Copy Rows Between Tables Without Creating Duplicates?. For more information, please follow other related articles on the PHP Chinese website!