How Can I Copy Rows Between Tables While Preventing Duplicate Entries?

Copying Rows Between Tables While Ignoring Duplicates

When working with multiple tables, it's often necessary to copy rows between them. However, it's crucial to prevent the insertion of duplicate rows. The following discussion addresses a user's issue with implementing this task.

Initially, the user attempted to use a WHERE clause with a subquery to identify non-duplicate rows. However, this approach yielded no results. Replacing the WHERE clause with a secondary key filtering was also considered, but it proved infeasible given the presence of multiple keys.

A viable solution involves modifying the subquery to include a predicate that identifies duplicate rows based on specific criteria. This ensures that only unique rows are inserted.

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

Alternatively, an outer join can be employed for greater conciseness:

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

These approaches address the issue of duplicate rows in the destination table. If, however, the concern is with duplicate rows within the source table, the following query may be used:

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

Lastly, it's recommended to explicitly list the fields in the INSERT statement rather than relying on the * wildcard to ensure data integrity.

The above is the detailed content of How Can I Copy Rows Between Tables While Preventing Duplicate Entries?. For more information, please follow other related articles on the PHP Chinese website!