如何在Oracle数据库中删除两条一模一样的重复数据？

SQL数据库运维 2022-11-25

3849

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

在处理Oracle数据库中的数据时，有时可能会出现不存在主键，导致数据重复插入同一张表内的情况，那么如何删除一张数据表内两条一模一样的数据中的一条并保留任意一条数据呢？今天我们就实际进行测试下。

方法一：使用临时表处理

在一张表内两条重复数据一模一样的情况下，可以用临时表来存放数据，首先，对原表进行删除操作后，然后，将临时表数据再次导入进原表。

准备数据，脚本如下：

--新建数据表 member_staging
CREATE TABLE member_staging (
    member_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    rank VARCHAR2(20)
);
--插入数据到 member_staging
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(7,'aobama','baideng','lundun');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(7,'aobama','baideng','lundun');
--查询数据
SELECT * FROM member_staging;

数据结果如下图，可以发现数据表内存在member_id号为7的两条一模一样的数据行：

查询是否存在重复数据，脚本如下：

--脚本查询出同一张表内存在多条相同数据的行
select member_id,first_name,last_name,rank from member_staging group by member_id,first_name,last_name,rank having count(1)>1;

处理原表数据之前进行原表数据的备份脚本，避免误删除造成数据丢失：

--备份原表数据(养成一个好习惯，任何操作之前先备份数据)
CREATE TABLE member_staging_BF AS
SELECT *  FROM member_staging;
--查询备份数据
SELECT * FROM member_staging_BF order by member_id;

用临时表 member_staging_temp 存放数据（临时表 member_staging_temp需要不重复的数据）

--用临时表 member_staging_temp 存放数据（临时表 member_staging_temp需要不重复的数据）
create table member_staging_temp as select member_id,first_name,last_name,rank from member_staging group by member_id,first_name,last_name,rank ORDER BY member_id;
--查询新建的临时表数据
SELECT * FROM  member_staging_temp;

删除原表member_staging的数据

--删除表 member_staging 数据
DELETE FROM  member_staging;

将member_staging_temp数据重新插入member_staging中

--将member_staging_temp数据重新插入member_staging中
insert into member_staging (select * from member_staging_temp);
--查询去重后的数据
SELECT * FROM  member_staging;

去重后的数据结果如下图所示：

方法二：使用oracle数据存放的物理地址进行删除操作

在oracle中，数据的物理地址记录在表的字段rowid中，两条数据的rowid是不同的，可以使用rowid来进行删除操作。

SELECT * FROM  member_staging a
where a.member_id in
( select member_id from member_staging group by member_id,first_name having count(1)>1 )
and rowid not in
( select min(rowid) from member_staging group by member_id,first_name having count(1)>1 );


SELECT * FROM member_staging a
where a.rowid != (
select max(rowid) from member_staging b where a.first_name=b.first_name and a.member_id=b.member_id
);

执行删除操作，并查询最终数据：

DELETE  member_staging a
where a.member_id in
( select member_id from member_staging group by member_id,first_name having count(1)>1 )
and rowid not in
( select min(rowid) from member_staging group by member_id,first_name having count(1)>1 );


DELETE member_staging a
where a.rowid != (
select max(rowid) from member_staging b where a.first_name=b.first_name and a.member_id=b.member_id
);


--查询最终删除的数据
select * from member_staging;

结果查询

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

如何在Oracle数据库中删除两条一模一样的重复数据？

评论