How to delete duplicate records in Sql

by Yogi P - December 3, 2022

Delete duplicate records in Sql

To delete duplicate records in SQL, you can use the following syntax:

DELETE FROM [table_name] WHERE [primary_key] IN (SELECT [primary_key] FROM [table_name] GROUP BY [primary_key] HAVING COUNT(*) > 1);

This statement will delete all duplicate records from the specified table, using the primary key column to identify duplicates.

For example, if you have a table named “students” with a primary key column named “id” and you want to delete all duplicate records from this table, you can use the following statement:

DELETE FROM students WHERE id IN (SELECT id FROM students GROUP BY id HAVING COUNT(*) > 1);

This statement will delete all duplicate records from the “students” table, using the “id” column as the primary key.

For example, if the “students” table contains the following records:

ID	Name
1	Alice
2	Bob
3	Carol
3	Carol

The statement above will delete the second record with an “id” of 3, leaving the table with the following records:

ID	Name
1	Alice
2	Bob
3	Carol

In this example, the statement identified all duplicate records using the “id” column as the primary key, and it deleted the duplicate records from the table.

In summary, this example shows how to use the DELETE FROM and GROUP BY statements to delete duplicate records from a table in SQL. Deleting duplicate records in SQL is a simple process that can be easily accomplished using the DELETE FROM and GROUP BY statements. This technique allows you to identify and delete all duplicate records from a table, using the primary key column to identify duplicates.