SQL
Identify and Delete Duplicate Records in a Table
Learn to effectively identify and remove duplicate rows from your SQL tables, ensuring data integrity and improving database performance by eliminating redundant entries.
DELETE FROM your_table
WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) as rn
FROM your_table
) as subquery
WHERE rn > 1
);
How it works: This snippet identifies and deletes duplicate rows based on the combination of `column1` and `column2`. It uses `ROW_NUMBER()` with a `PARTITION BY` clause to assign a unique rank to each row within a group of identical `column1` and `column2` values. Rows with a rank greater than 1 are considered duplicates and are subsequently deleted, keeping only the first occurrence.