How Can I Copy Rows from One Table to Another While Preventing Duplicates?

Copying Rows to a Table while Avoiding Duplicates

The goal is to transfer all rows from one table (srcTable) to another (destTable), excluding any duplicate entries. While it may seem straightforward to add a WHERE clause with a subquery that filters out duplicates, this approach can fail.

Subquery Issue:

The original query is as follows:

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

However, this query does not insert any rows because the subquery returns all rows from destTable, regardless of whether they match any rows in srcTable. To make the subquery work, it must include an additional condition to identify duplicate rows based on relevant fields.

Revised Subquery:

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 revised subquery compares specific fields to determine duplicates and excludes them from insertion.

Outer Join Alternative:

An alternate approach is to use an outer join:

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

This outer join technique filters out rows from srcTable that have matching keys in destTable, excluding duplicates during insertion.

Addressing Source Table Duplicates:

If the concern is that sourceTable contains duplicate rows, the query should be modified to select distinct rows:

INSERT INTO destTable
SELECT DISTINCT Field1, Field2, Field3,...
FROM srcTable  

Including specific field names in the INSERT statement is recommended over using SELECT *.

The above is the detailed content of How Can I Copy Rows from One Table to Another While Preventing Duplicates?. For more information, please follow other related articles on the PHP Chinese website!