How to Efficiently Remove Duplicate Rows Except for the Earliest Instance?

Efficiently Removing Duplicate Rows Except for Earliest Instance

Problem:

You have a table containing data with numerous duplicate entries caused by user submissions. Your goal is to eliminate these duplicate rows based on the subscriberEmail field, leaving only the earliest submitted record. In other words, you want to identify all duplicate emails and delete their corresponding rows while preserving the original submission.

Solution:

1. Self-Join Approach:

Instead of swapping tables, you can leverage a self-join to achieve your goal:

<code class="sql">delete x
from myTable x
join myTable z on x.subscriberEmail = z.subscriberEmail
where x.id > z.id</code>

In this query:

• x and z are aliases that represent two instances of the same table.
• The JOIN clause matches records based on matching subscriberEmail values.
• The WHERE clause selects records from x where the id is greater than the id of the corresponding record in z. This ensures that only duplicate rows with higher IDs are selected for deletion.

2. Additional Considerations:

To prevent future duplicate insertions, consider creating a UNIQUE INDEX on the subscriberEmail column.

Benefits:

This approach efficiently removes duplicate rows without the overhead of creating a temporary table. It utilizes the existing table structure and preserves the unique identity of each row based on the id field.

The above is the detailed content of How to Efficiently Remove Duplicate Rows Except for the Earliest Instance?. For more information, please follow other related articles on the PHP Chinese website!