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

Deleting Duplicate Records from a SQL Table without a Primary Key

In a SQL table without a primary key, identifying and removing duplicate records can be a complex task. The problem arises when multiple rows share the same values in specific columns, creating redundancies in the data. To resolve this, we will explore a query that effectively eliminates duplicate rows based on a combination of columns.

Consider the following table named "employee" with the given data:

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');

To remove duplicate rows based on "EmpId" and "EmpSSN," we can leverage 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

Explanation:

1. The inner query calculates the row number for each row based on the partitioning columns "EmpId," "EmpName," and "EmpSSN." This step identifies the order of rows for each unique combination of these fields.
2. The outer query then removes rows where the row number "cnt" is greater than 1, indicating that there are duplicate rows for that specific combination.

Executing this query would effectively remove duplicate records from the "employee" table while preserving the original order of the data. After execution, the table would contain only unique rows based on the "EmpId" and "EmpSSN" fields.

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