How to Remove Duplicate Rows from a SQL Table Without a Primary Key?

How to Delete Duplicate Records from a SQL Table Without a Primary Key

When dealing with SQL tables that lack a primary key, handling duplicate records can pose a challenge. However, there are efficient methods to eliminate duplicates based on specific criteria.

Consider the following scenario: you have a 'employee' table with columns 'EmpId,' 'EmpName,' and 'EmpSSN,' and some records contain duplicate values in the 'EmpId' and 'EmpSSN' fields. To remove these duplicates, you can utilize the following query:

DELETE SUB FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt
 FROM employee) SUB
WHERE SUB.cnt > 1

Understanding the Query:

• The outer DELETE statement removes rows from the 'employee' table based on information retrieved from the subquery.
• The subquery calculates a row count (cnt) for each unique combination of 'EmpId,' 'EmpName,' and 'EmpSSN.'
• The PARTITION BY clause groups records by these fields to ensure that the row count is reset for each distinct group.
• The ORDER BY clause sorts records within each group to ensure that the first row is the original record.
• The WHERE clause in the subquery filters out rows with a cnt greater than 1, indicating that they are duplicates.
• The outer DELETE statement then uses the FROM clause to reference the subquery as a virtual table (SUB), and deletes those rows from the main 'employee' table.

This query effectively removes duplicate records based on the unique combination of 'EmpId' and 'EmpSSN,' efficiently performing data cleanup in tables without a primary key.

The above is the detailed content of How to Remove Duplicate Rows from a SQL Table Without a Primary Key?. For more information, please follow other related articles on the PHP Chinese website!