Back to SDET Scenarios
Q12Question 12 of 12

SQL query to find duplicates and safely delete them keeping one row

Key Concepts
  • Find duplicates with GROUP BY key HAVING COUNT(*) > 1 to identify and quantify duplicated rows
  • Delete safely using a self-join that removes rows with id greater than the minimum for the same key
  • The ROW_NUMBER CTE approach is more portable: partition by key, order by tie-break, delete where rn > 1
  • Always back up first, wrap in a transaction, verify the affected count, and only then commit
  • Choose the survivor by a business rule: earliest record, most recently updated, or most complete data
  • After cleanup, add a unique constraint on the natural key to prevent duplicates from recurring

Definition

Find duplicates with GROUP BY key HAVING COUNT greater than one, then delete the extras safely by keeping the MIN or MAX id via a self-join or a ROW_NUMBER CTE, always inside a transaction and after a backup.
  • GROUP BY HAVING – Groups rows by the natural key and keeps groups with count greater than one to find duplicates.
  • Self-join deletion – Deletes rows whose id is greater than the minimum for the same key, keeping the earliest survivor.
  • ROW_NUMBER CTE – Numbers rows within each key group and deletes those numbered greater than one. More portable and readable.
  • Safety discipline – Always back up first, wrap in a transaction, verify the affected count before committing.
  • Survivor selection – Choose deterministically by a business rule: earliest, most recent, or most complete.
  • Prevention – Add a unique constraint on the natural key after cleanup to prevent recurrence.

Imagine the National Bank of India's account table accidentally has the same account recorded three times because of a buggy import. First the tester asks "which accounts appear more than once?" by grouping rows by account number and keeping only groups with a count above one. Then, to clean up, the bank must keep exactly one copy of each account and delete the extras, but very carefully, because deleting the wrong rows loses real data. So the bank takes a backup first, wraps the delete in a transaction that can be rolled back if something looks wrong, and deletes only the extra copies while keeping, say, the one with the smallest identifier.

Understanding the Concepts

What does "duplicate" mean in a database?

A duplicate is defined by the natural key, the set of columns that should be unique together. For an accounts table that might be the account number. For transactions it might be account, amount, and timestamp.

SQL
-- Define the natural key before detecting duplicates
-- For accounts: account_no should be unique
-- For transactions: (account_id, amount, txn_date) should be unique

How do you detect duplicates?

Group by the natural key and filter groups with more than one row.

SQL
-- Detect duplicate accounts
SELECT account_no, COUNT(*) AS duplicate_count
FROM accounts
GROUP BY account_no
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;
DIAGRAM
account_no   | duplicate_count
─────────────┼────────────────
ACC-78901    | 3
ACC-45678    | 2

How do you delete with a self-join?

The self-join keeps the row with the minimum id per key by deleting any row that has another row with the same key but a smaller id.

SQL
-- Self-join: keep the minimum id per account_no
DELETE a
FROM accounts a
INNER JOIN accounts b
  ON a.account_no = b.account_no
  AND a.id > b.id;

-- This deletes every row that is NOT the minimum for its key
-- Leaving exactly one survivor per duplicated group

How do you delete with ROW_NUMBER CTE?

The ROW_NUMBER CTE is more portable and readable. Partition by key, order by a tie-break column, delete rows numbered greater than one.

SQL
-- ROW_NUMBER CTE: keep the earliest record per account_no
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY account_no
           ORDER BY id ASC
         ) AS rn
  FROM accounts
)
DELETE FROM accounts
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

-- To keep the most recently updated:
-- ORDER BY updated_at DESC  (keeps the latest)

How do you wrap it safely in a transaction?

Always back up first, wrap in a transaction, and verify before committing.

SQL
-- Step 1: Backup
CREATE TABLE accounts_backup AS SELECT * FROM accounts;

-- Step 2: Transaction
BEGIN;

-- Step 3: Delete duplicates
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY account_no ORDER BY id ASC
         ) AS rn
  FROM accounts
)
DELETE FROM accounts
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- Step 4: Verify
SELECT account_no, COUNT(*)
FROM accounts
GROUP BY account_no
HAVING COUNT(*) > 1;
-- Should return 0 rows

-- Step 5: Commit only if verified
COMMIT;
-- Or ROLLBACK if something looks wrong

How do you handle foreign key references?

If other tables reference the rows you are deleting, re-point them to the survivor first.

SQL
-- Re-point transactions to the surviving account row
UPDATE transactions t
SET account_id = (
  SELECT MIN(a.id) FROM accounts a
  WHERE a.account_no = (
    SELECT account_no FROM accounts WHERE id = t.account_id
  )
)
WHERE t.account_id NOT IN (
  SELECT MIN(id) FROM accounts GROUP BY account_no
);

-- Now safe to delete duplicate account rows

How do you prevent recurrence?

Add a unique constraint after cleanup.

SQL
-- Prevent future duplicates
ALTER TABLE accounts
ADD CONSTRAINT uq_account_no UNIQUE (account_no);

How do you handle large tables?

Delete in batches to avoid long locks and transaction-log bloat.

SQL
-- Batch delete: 1000 rows at a time
REPEAT
  DELETE FROM accounts
  WHERE id IN (
    SELECT id FROM (
      WITH ranked AS (
        SELECT id, ROW_NUMBER() OVER (
          PARTITION BY account_no ORDER BY id ASC
        ) AS rn
        FROM accounts
      )
      SELECT id FROM ranked WHERE rn > 1
      LIMIT 1000
    ) AS batch
  );
UNTIL ROW_COUNT() = 0;

What are common interview follow-up questions?

  • "What if you want to keep the most recently updated row?" Order by updated_at DESC in the ROW_NUMBER window.
  • "What about performance on millions of rows?" Delete in batches, use an index on the natural key, and consider partitioning.
  • "How do you verify the delete was correct?" Re-run the detection query: it should return zero rows.

Key Takeaway

To find duplicates I first decide what uniqueness means, the natural key, then group by that key and keep only the groups with more than one row, SELECT key, COUNT star FROM table GROUP BY key HAVING COUNT star greater than one. That both confirms the problem and quantifies it, telling me which keys are duplicated and how many copies exist, before I touch anything.

Deleting the duplicates is the delicate part, because a DELETE is irreversible once committed, so I wrap it in a safety discipline. I take a backup, or at least snapshot the affected rows into a backup table, I run inside an explicit transaction so I can check the affected count and roll back if it looks wrong, I choose the survivor deterministically, and I verify afterward. For the delete itself I use one of two standard techniques. The self-join keeps the minimum identifier per key by deleting any row that has another row with the same key and a smaller identifier, DELETE a FROM accounts a JOIN accounts b ON a.account_no equals b.account_no AND a.id greater than b.id. The more portable and readable technique is a ROW_NUMBER common table expression that partitions by the key, orders by a tie-break column, and deletes the rows numbered greater than one, where ordering by id ascending keeps the smallest, descending keeps the largest, and ordering by updated_at keeps the most recent. After the delete I re-run the detection query to confirm zero duplicates remain, and I add a unique constraint on the natural key so duplicates cannot come back.

The senior points I always raise are to pick the survivor by a business rule rather than arbitrarily, to re-point or cascade any foreign keys to the survivor before deleting so I do not orphan data, and to delete in batches on very large tables to avoid long locks and transaction-log bloat. At the National Bank of India a buggy import once duplicated account rows, and I cleaned it exactly this way, detection query, backup table, ROW_NUMBER delete keeping the earliest record inside a transaction I verified before committing, and a new unique constraint to prevent recurrence, so no real account data was lost. Being able to write the detection query and both deletion techniques and to wrap them in backup, transaction, verify, and constraint is what shows an interviewer I treat a destructive data fix with the care a bank demands.

Remember one sentence: find duplicates with GROUP BY HAVING, delete safely with ROW_NUMBER inside a transaction after a backup, and prevent recurrence with a unique constraint.

---

Visual Overview

DIAGRAM
  Finding and Deleting Duplicates Safely
  ──────────────────────────────────────

  Step 1: DETECT
  ┌──────────────────────────────────────────┐
  │ SELECT key, COUNT(*)                     │
  │ FROM accounts                            │
  │ GROUP BY key                             │
  │ HAVING COUNT(*) > 1                      │
  │ -> Shows which keys are duplicated       │
  └──────────────────────────────────────────┘

  Step 2: BACKUP
  ┌──────────────────────────────────────────┐
  │ CREATE TABLE accounts_backup AS          │
  │ SELECT * FROM accounts                   │
  └──────────────────────────────────────────┘

  Step 3: DELETE (inside transaction)
  ┌──────────────────────────────────────────┐
  │ Self-join:                               │
  │   DELETE a FROM accounts a               │
  │   JOIN accounts b ON a.key = b.key       │
  │   AND a.id > b.id                        │
  │                                          │
  │ ROW_NUMBER CTE:                          │
  │   WITH ranked AS (                       │
  │     SELECT id, ROW_NUMBER() OVER         │
  │     (PARTITION BY key ORDER BY id) AS rn │
  │   ) DELETE WHERE rn > 1                  │
  └──────────────────────────────────────────┘

  Step 4: VERIFY + COMMIT
  Step 5: ADD UNIQUE CONSTRAINT
Share
Use ← → arrow keys to navigate