Cleaning Up Your Database: Removing Duplicates While Preserving Data (SQL)

Imagine a table with data like customer names and emails. Sometimes, the same customer information might be entered multiple times, creating duplicates. You want to remove these duplicates but keep at least one record for each unique customer.

The Solution:

T-SQL offers a couple of ways to achieve this:

1. Using ROW_NUMBER() and DELETE:

This method identifies duplicate rows and then deletes all but the first occurrence for each group of duplicates. Here's a breakdown:

• We use the ROW_NUMBER() function to assign a unique number to each row based on a specific order (e.g., by customer ID and email).
• We then use a DELETE statement that targets rows with a ROW_NUMBER() greater than 1. This effectively deletes all rows except the first one (which has a ROW_NUMBER() of 1) for each set of duplicates.

1. Using EXISTS and DELETE:

This approach utilizes a subquery to identify rows with duplicates. Here's the idea:

• We use a DELETE statement with a WHERE clause.
• Inside the WHERE clause, we have an EXISTS subquery that checks if there's another row with the same data (excluding the current row).
• If the subquery finds a duplicate, the current row is deleted.

Important Points:

• Both methods will keep the "first" encountered duplicate based on the chosen ordering within the query.
• It's recommended to test the logic with a SELECT statement before executing the actual DELETE to ensure it targets the desired rows.
• Consider backups before modifying your data.

-- Assuming your table is named 'Customers' and has columns 'CustomerID' and 'Email' for identification

DELETE FROM Customers
WHERE ROW_NUMBER() OVER (PARTITION BY CustomerID, Email ORDER BY CustomerID) > 1;

This code first assigns a row number within each group of duplicate customer IDs and emails (ordered by customer ID). Then, it deletes any row with a ROW_NUMBER() greater than 1, effectively keeping only the first occurrence of each unique customer-email combination.

Method 2: Using EXISTS and DELETE

-- Assuming your table is named 'Customers' and has columns 'CustomerID' and 'Email' for identification

DELETE FROM Customers c
WHERE EXISTS (
  SELECT 1
  FROM Customers c2
  WHERE c2.CustomerID = c.CustomerID AND c2.Email = c.Email AND c2.ID <> c.ID
);

This code uses a subquery within the DELETE statement. The subquery checks if there's another row (identified by c2) with the same CustomerID and Email as the current row (c), but with a different ID (c2.ID <> c.ID ensures it's not the same row). If a duplicate exists, the outer DELETE statement removes the current row (c).

Remember:

• Replace Customers, CustomerID, and Email with your actual table and column names.
• Adjust the ORDER BY clause in method 1 if you want to keep duplicates based on a different order.

This method avoids modifying the original table directly. Instead, it creates a new table containing only distinct rows. Here's how it works:

-- Assuming your table is named 'Customers' and has columns for identification (e.g., 'CustomerID' and 'Email')

CREATE TABLE UniqueCustomers (
  -- Define columns for the new table with same data types as original
  CustomerID [data type],
  Email [data type],
  -- Add additional columns if needed
);

INSERT INTO UniqueCustomers
SELECT DISTINCT CustomerID, Email
-- Add additional columns for selection if needed
FROM Customers;

DROP TABLE Customers; -- Replace with RENAME TABLE if needed
RENAME TABLE UniqueCustomers TO Customers; -- Replace with appropriate action

This approach:

1. Creates a new table UniqueCustomers with the same structure as the original table.
2. Uses SELECT DISTINCT to insert only unique rows from Customers into the new table.
3. Drops (or renames) the original table.
4. Renames the UniqueCustomers table back to Customers.

Using LEFT JOIN and DELETE:

This method identifies rows to keep based on a specific column (e.g., one with a timestamp). It keeps the row with the earliest (or latest) timestamp for duplicates. Here's an example:

-- Assuming your table is named 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate'

DELETE o
FROM Orders o
LEFT JOIN Orders o2
  ON o.CustomerID = o2.CustomerID AND o.OrderID <> o2.OrderID
WHERE o2.OrderDate < o.OrderDate;  -- Keeps the earliest order for duplicates (change '<' for latest)

1. Uses a LEFT JOIN to compare each row (o) with other rows (o2) having the same CustomerID but different OrderID.
2. The WHERE clause keeps only rows from o where there's no matching row in o2 with an earlier (OrderDate < o.OrderDate) timestamp. This ensures it keeps the first (or last, depending on the comparison) order for each customer.

• Choose the method that best suits your needs and data structure.
• Consider factors like performance and potential data loss (when dropping tables).
• Adjust the code based on your specific table structure and desired logic for keeping duplicates.