How to Delete Duplicate Records in a SQL Table Without a Primary Key?

Delete Duplicate Records in SQL Table Without Primary Key

In the absence of a primary key, identifying duplicate records in a database can be challenging. However, utilizing a combination of window functions and logical operators allows for efficient deletion of duplicates.

In the provided example, the goal is to remove records from the Employee table that have the same EmpId and EmpSSN values. To achieve this:

1. Create a temporary column using the ROW_NUMBER() function to assign a count to each record based on the partitioning by EmpId, EmpName, and EmpSSN fields. This creates a table where each unique combination has a unique count.

ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt

2. Create a subquery that filters the table to only include records where the count is greater than 1, indicating duplicate records.

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

3. Use the DELETE statement to remove the duplicate records from the Employee table based on the results of the subquery.

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

By following these steps, the duplicate records will be effectively deleted from the Employee table, ensuring the integrity of the data without requiring a primary key.

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