TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
SQL ROW_NUMBER for duplicate review: establish the right order before deleting
Software Engineering · 2022Photo: Unsplash / Unsplash ↗
Software Engineering2022 / RETROSPECTIVE

SQL ROW_NUMBER for duplicate review: establish the right order before deleting

A practical duplicate-review workflow using CTE, ROW_NUMBER, clear retention rules and rollback.

Yazılımcının GünlüğüPublished: Nov 16, 20263 min read

Sample period date: Sep 19, 2022. These essays revisit earlier technologies from today's editorial perspective; they are not represented as posts originally published in those years.

Business rules define duplicates

Matching names do not necessarily indicate duplicate records. Two customers may share a name, and one customer may legitimately have multiple addresses. Define the identity key, grouping rule and retention policy before changing data. An arbitrary ordering risks losing valid records.

ROW_NUMBER ranks records; it does not deduplicate by itself

ROW_NUMBER() OVER(PARTITION BY CustomerKey ORDER BY UpdatedAt DESC, Id DESC) can rank rows within each customer. You may inspect the first row as a candidate to retain. Ties in timestamps require a deterministic secondary key such as Id. Review the output with SELECT before considering a DELETE.

Follow foreign-key references before removal

An older record may be linked to invoices, comments or transaction history. Deleting from the primary table can break those references. A mapping table, a rollback plan and reference updates may be needed. Often a controlled merge is safer than a simple delete. Exercise the entire workflow in a test database first.

Prevent the problem from returning

A one-time cleanup is not a durable solution. Add an appropriate UNIQUE constraint, normalize identifiers and make upserts idempotent. Measure success by referential integrity as well as fewer rows. Cleanup and ongoing integrity are distinct but connected responsibilities.

0 reads

Comments

0 real comments

No real reader comments yet.

✎ Write a comment

Comments are reviewed before publication.