Blog

Welcome, fellow data enthusiasts! Today, we’re going to tackle a common yet pesky problem that plagues many a database: duplicate records. Fear not, for I shall guide you through the process of removing these unwelcome guests from your MySQL tables with surgical precision.

Identifying Duplicates: The First Step to Clarity

Alright, detectives! Let’s put our newfound knowledge to the test. We’ll use a sample table called customers with columns for customer_id (primary key), name, email, and city. Here’s some sample data, including those pesky duplicates:

customer_id	name	email	city
1	John Smith	john.smith@email.com	New York
2	Jane Doe	jane.doe@email.com	Chicago
3	Michael Lee	michael.lee@email.com	Los Angeles
4	John Smith	john.smith@email.com	New York (Duplicate!)
5	Jane Doe	jane.doe@email.com	Seattle

In the above table we have 2 emails which seem duplicate.

1. john.smith@email.com
2. jane.doe@email.com

But if you check the city column, then jane.doe@email.com is not actually duplicate because the city values are different which is Chicago and Seattle.

So the only duplicate data would be john.smith@email.com. So that is the data that we want to remove from out table.

Finding the Culprits:

Before we can banish duplicates, we must first shine a light on them. Run this simple query to spot the culprits:

SELECT * FROM customers
GROUP BY email, city
HAVING COUNT(*) > 1;

Let’s break down this query

1. *SELECT : This part instructs the database to retrieve all columns (*) from the customers table.
2. FROM customers: This specifies the table from which we want to retrieve data.
3. GROUP BY email, city: This clause groups the rows together based on two columns: email and city. Imagine sorting the customer data first by email address, and then within each email group, sorting by city. This creates groups of rows with the same email address and city combination.
4. HAVING COUNT(*) > 1: This clause acts as a filter on the groups created by the GROUP BY. The HAVING clause is used specifically with aggregation functions (like COUNT(*) in this case) applied to grouped data. Here, COUNT(*) counts the number of rows within each group. The condition HAVING COUNT(*) > 1 filters the grouped results to show only those groups where the count is greater than 1. In simpler terms, it will only display groups where there are duplicate email addresses for a specific city (e.g., multiple “John Smith” entries with the same email in New York).

The Deletion Spell: A Gentle Touch for a Tough Job (Cautious!)

With our unique identifiers in place, it’s time to perform the deletion. But worry not, we’ll do this with the grace of a seasoned SQL sorcerer:

Remember: This will permanently delete duplicate rows. Make sure you have a backup! You know your table best so it would be your responsibility to make edits to this query and make sure you are only running tested queries in environments where precaution is necessary. Now with, that out of the way, let’s clean up the unwanted duplicate records.

SET SQL_SAFE_UPDATES=0;  -- Temporarily disable Safe Update mode

DELETE t1 FROM customers AS t1
INNER JOIN customers AS t2 ON t1.email = t2.email AND t1.city = t2.city AND t1.customer_id > t2.customer_id;

SET SQL_SAFE_UPDATES=1;  -- Re-enable Safe Update mode (optional)

Let’s now breakdown this delete statement

DELETE t1 FROM customers AS t1: This part initiates the deletion process.

• DELETE: Keyword indicating the operation we want to perform (deletion).
• t1: This is an alias assigned to the customers table for easier reference within the query.
• FROM customers AS t1: Specifies the table from which we want to delete rows (customers) and assigns the alias t1 to it.

INNER JOIN customers AS t2 ON t1.email = t2.email AND t1.city = t2.city AND t1.customer_id > t2.customer_id: This clause accomplishes two things:

• INNER JOIN: This specifies a join operation between two tables (here, both referencing the customers table with aliases t1 and t2). An inner join only returns rows where there’s a match in both tables based on the specified criteria.

Join Conditions:

• t1.email = t2.email: This condition ensures that the emails in both tables (represented by aliases t1 and t2) must match for a join to occur.
• t1.city = t2.city: This adds another layer of filtering to the join. Only rows where both emails and cities match will be joined.
• t1.customer_id > t2.customer_id: This crucial part determines which duplicate record gets deleted. By specifying t1.customer_id to be greater than t2.customer_id, we ensure that the record with the higher customer ID (likely the newer duplicate) gets deleted from table t1 (aliased as customers).

In simpler terms, this DELETE statement identifies duplicate customer records based on matching email and city. Then, it specifically removes the record with the higher customer ID from each duplicate pair, effectively keeping the record with the lower ID.

Finally let’s make sure we never have duplicates again

This approach is preventative. Once you’ve cleaned your data, create a unique index on the email column:

ALTER TABLE customers ADD UNIQUE INDEX unique_email_city (email, city);

As the database query forensics that we are, let’s also break down this query

This query is used to create a unique index on a combination of columns in a MySQL table. Let’s break it down:

ALTER TABLE customers: This part specifies that we want to modify the structure of the table named customers.

ADD UNIQUE INDEX: This indicates that we’re adding a new index to the table, and this specific index will enforce uniqueness on the specified columns.

unique_email_city (email, city):

• unique_email_city: This is the name assigned to the unique index. You can choose a name that reflects its purpose (in this case, it ensures unique combinations of email and city).
• (email, city): This part defines the columns that will be included in the unique index. Here, we’re creating an index on both the email and city columns.

A unique index acts like a fingerprint for the specified columns in a table. When a new row is inserted, the database checks the unique index to see if there’s already a matching combination of values in the indexed columns. If a duplicate is found (based on both email and city in this case), the database will prevent the insertion from happening.

Conclusion

And there you have it, comrades in code! With these SQL statements at your disposal, you can banish those pesky duplicate records from your MySQL tables faster than you can say “data deduplication.” Remember, a clean database is a happy database (and a much faster one too).

So go forth, declutter your digital domains, and prevent future twin troubles with that handy unique index. Just be sure to back up your data before any major deletions – nobody wants an accidental case of “missing database sock syndrome.”

Happy de-duplicating!

---

---

---

---