How to Remove Duplicate Rows in a Table While Keeping the Oldest Entry?

Removing Duplicate Rows While Preserving Oldest Entries

In your table containing user submission data, duplicate entries have accumulated based on the subscriberEmail field. To ensure data integrity, you want to remove these duplicate rows while retaining the original submissions.

Solution:

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

Explanation:

• Self-Join: The query performs a self-join on the myTable table, creating two separate instances of the table, aliased as "x" and "z."
• Comparison Condition: The join matches rows from "x" and "z" based on the subscriberEmail column, effectively comparing each row against all other rows.
• Filtering: The where clause selects rows from "x" where the id column value is greater than the id value in the corresponding row in "z." This identifies duplicate rows with higher ID values.
• Deletion: The delete statement removes the duplicate rows identified in "x," leaving only the original submissions.

Additional Considerations:

To prevent future duplicate insertions, consider creating a UNIQUE index on the subscriberEmail column. This will enforce the uniqueness of email addresses in the table.

The above is the detailed content of How to Remove Duplicate Rows in a Table While Keeping the Oldest Entry?. For more information, please follow other related articles on the PHP Chinese website!