从没有主键的 SQL 表中删除重复记录 [英] Delete duplicate records from a SQL table without a primary key
本文介绍了从没有主键的 SQL 表中删除重复记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下表,其中有以下记录
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.
例如: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屋!
查看全文