SQL

Delete Duplicate Rows While Keeping One

Learn to remove duplicate records from a SQL table, ensuring that one unique instance of each duplicated set is preserved for data cleanliness.

WITH CTE_Duplicates AS (
  SELECT
    id, column1, column2,
    ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) as rn
  FROM
    YourTable
)
DELETE FROM
  CTE_Duplicates
WHERE
  rn > 1;

How it works: This SQL query demonstrates how to delete duplicate rows from a table while preserving one unique instance. It uses a Common Table Expression (CTE) with the `ROW_NUMBER()` window function. `ROW_NUMBER()` assigns a sequential integer to rows within each partition (defined by `column1`, `column2` for duplication), ordered by `id`. Any row with `rn > 1` is a duplicate beyond the first occurrence and is subsequently deleted, leaving only one record per unique `(column1, column2)` combination.

Delete Duplicate Rows While Keeping One

Need help integrating this into your project?