SQL
Delete Duplicate Records While Retaining One Instance
Efficiently remove duplicate rows from a SQL table, preserving a single unique entry based on a chosen primary key or unique identifier, for robust data cleanup.
DELETE t1
FROM YourTable t1
INNER JOIN YourTable t2 ON
t1.Column1 = t2.Column1 AND
t1.Column2 = t2.Column2 AND
t1.PrimaryKeyColumn > t2.PrimaryKeyColumn;
How it works: This SQL snippet effectively removes duplicate records from a table, ensuring only one instance of a set of duplicate values remains. It works by joining the table to itself on the columns that define a duplicate (Column1, Column2) and then deleting rows where the PrimaryKeyColumn (or any unique identifier) is greater, effectively keeping the row with the minimum PrimaryKeyColumn value. This method is compatible with SQL Server and MySQL.