从没有主键的SQL表中删除重复记录 [英] Delete duplicate records from a SQL table without a primary key

问题描述

我有下面的表格，其中有以下记录

I have the below table with the below records in it

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

我没有任何主键在这个表中。但是我已经在我的表中有上述记录。
我想删除在EmpId和EmpSSN字段中具有相同值的重复记录。

I dont have any primary key in this table .But i have the above records in my table already. I want to remove the duplicate records which has the same value in EmpId and EmpSSN fields.

Ex：Emp id 5

Ex : Emp id 5

任何人都可以帮助我建立一个查询来删除这些重复记录。

Can any one help me to frame a query to delete those duplicate records

提前感谢

推荐答案

添加主键（代码如下）

运行正确的删除（下面的代码）

Run the correct delete (code below)

考虑为什么你不想保留主键。

Consider WHY you woudln't want to keep that primary key.

假设MSSQL或兼容：

Assuming MSSQL or compatible:

ALTER TABLE Employee ADD EmployeeID int identity(1,1) PRIMARY KEY;

WHILE EXISTS (SELECT COUNT(*) FROM Employee GROUP BY EmpID, EmpSSN HAVING COUNT(*) > 1)
BEGIN
    DELETE FROM Employee WHERE EmployeeID IN 
    (
        SELECT MIN(EmployeeID) as [DeleteID]
        FROM Employee
        GROUP BY EmpID, EmpSSN
        HAVING COUNT(*) > 1
    )
END

这篇关于从没有主键的SQL表中删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！