How do I remove duplicate rows based on subscriberEmail while keeping the oldest submission?

Removing Duplicate Rows While Preserving Oldest Submissions

To eliminate duplicate rows based on the subscriberEmail field, while retaining the original submission, follow these steps:

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

Explanation:

• This query joins the table against itself using the join clause. This creates two instances of the table, aliased as "x" and "z."
• The where clause compares the id values of the two instances. Rows in instance "x" with an id greater than the corresponding row in instance "z" represent duplicate submissions.
• The delete statement identifies these duplicate rows in instance "x" and removes them from the table.
• The result is a table with unique subscriber emails, where only the original submission (with the lowest id) remains for each email address.

Additional Notes:

• To prevent future duplicates, consider creating a UNIQUE index on the subscriberEmail column.
• The above query can be simplified by combining the join and where clauses:

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

The above is the detailed content of How do I remove duplicate rows based on subscriberEmail while keeping the oldest submission?. For more information, please follow other related articles on the PHP Chinese website!