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

Handling Duplicate Records in SQL Tables Without Primary Keys

In the absence of a primary key, deleting duplicate records from a SQL table can be challenging. Let's consider the following scenario:

A table named "employee" contains the following records without a primary key:

create table employee
(
 EmpId number,
 EmpName varchar2(10),
 EmpSSN varchar2(11)
);

insert into employee values(1, 'Jack', '555-55-5555');
insert into employee values (2, 'Joe', '555-56-5555');
insert into employee values (3, 'Fred', '555-57-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');
insert into employee values (1, 'Jack', '555-55-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6 ,'Lisa', '555-70-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');

Given this table, the goal is to delete the duplicate records while preserving unique ones. To achieve this, we can use a combination of a "ROW_NUMBER()" function and a "DELETE" statement:

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

Breaking down the code:

• The inner query uses the ROW_NUMBER() function to assign a sequential number to each row for each group of duplicate records. ThePARTITION BY clause partitions the data by the EmpId, EmpName, and EmpSSN columns, and the ORDER BY EmpId ensures that rows are numbered in ascending order of EmpId.
• The outer query uses the cnt column from the inner query as a filter to select only the duplicate rows (cnt > 1).
• The DELETE statement uses the subquery to delete the duplicate rows from the original table.

By executing this query, the duplicate records in the "employee" table will be removed, leaving only the unique records:

select * from employee;

EmpId EmpName EmpSSN
1      Jack   555-55-5555
2      Joe    555-56-5555
3      Fred   555-57-5555
4      Mike   555-58-5555
5      Cathy  555-59-5555
6      Lisa   555-70-5555

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