Find Duplicate Records in SQL Before You Delete Anything

Repeated values are not always duplicate records. A customer can place several orders, and two customers can share a city. Before deleting anything, define which fields identify the same business event and inspect the records that violate that rule.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Begin with an intentional duplicate example

WITH imports(source_row, reference, amount) AS (
  VALUES (1, 'R101', 1200),
         (2, 'R102', 800),
         (3, 'R101', 1200),
         (4, 'R103', 600)
)
SELECT reference, COUNT(*) AS occurrences
FROM imports
GROUP BY reference
HAVING COUNT(*) > 1
ORDER BY reference;

The result is R101 with 2 occurrences. This identifies repeated references in the import. It does not yet prove which row should be retained or whether reference alone is the correct business key.

Inspect the complete candidate rows

WITH imports(source_row, reference, amount) AS (
  VALUES (1, 'R101', 1200),
         (2, 'R102', 800),
         (3, 'R101', 1200),
         (4, 'R103', 600)
), repeated AS (
  SELECT reference FROM imports
  GROUP BY reference HAVING COUNT(*) > 1
)
SELECT i.source_row, i.reference, i.amount
FROM imports AS i
JOIN repeated AS r ON r.reference = i.reference
ORDER BY i.reference, i.source_row;

Rows 1 and 3 appear, both with R101 and 1200. Keeping the source-row identifier makes the candidates traceable. If the amounts differed, you would need to resolve a conflict rather than simply remove an identical copy.

Choose the duplicate definition deliberately

An exact duplicate means all relevant fields match. A repeated business key means the identifier repeats, potentially with conflicting details. A similar name is only a clue. The deletion rule should come from the data's purpose and authoritative records.

In the shop database, customer 1 appears twice in orders because Amina placed two different orders. Those rows have different order IDs and amounts. Removing one because customer_id repeats would destroy legitimate activity.

Do not use DISTINCT as a hidden repair

SELECT DISTINCT can produce a unique result list, but the original problem remains in the source. If a duplicate import doubled a financial total, hiding repeated output rows may not fix every downstream calculation. Record the cause and correct the source under an agreed procedure.

For a real repair, keep a backup, identify the exact rows, decide which version is authoritative and validate totals after the change. This lesson intentionally stays read-only. Discovering candidates is a useful complete step before any deletion is considered.

Reconcile counts before and after a proposed repair

The example has four imported rows and three unique references. If one confirmed duplicate were removed, three rows would remain. That arithmetic is a check, not proof that removal is justified. Preserve an audit record linking the retained and removed source rows.

Try it yourself

Find repeated customer_id values in the original orders table. Return customer_id and count. Explain why the resulting customer is not automatically a duplicate-data problem.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY customer_id;

Keep exploring: SQL learning path.