【oracle】删除某表名称重复记录的两种方法

实验表：

create table dupo(
id int,
name nvarchar2(20),
create_time timestamp default sysdate,
primary key(id));

实验数据：

insert into dupo(id,name) values(1,'andy');
insert into dupo(id,name) values(2,'andy');
insert into dupo(id,name) values(3,'andy');
insert into dupo(id,name) values(4,'Bill');
insert into dupo(id,name) values(5,'Bill');
insert into dupo(id,name) values(6,'Bill');
insert into dupo(id,name) values(7,'Bill');
insert into dupo(id,name) values(8,'Cindy');
insert into dupo(id,name) values(9,'Cindy');
insert into dupo(id,name) values(10,'Douglas');

由上可见，有三个andy，三个bill，两个cindy是重复了，现在就当它们是人工入力错误，需要用SQL将其删除。

第一种方法，我们可以将dupo变成左右表，删除名称相同而id大于或是小于左表id的记录。

delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id<a.id)
delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id>a.id)

上面两句都可以达到目的，但留下的数据id不同，第一条是留最小的，第二条是留最大的。

以上删除如果数据量大，需要给id，name加上联合索引。

第二种方法：利用rowid

delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid<a.rowid)
delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid>a.rowid)

与第一种方法比较即可得知，两种方法不同即在于用id还是rowid。

这种删除如果数据量大，只用在name上加单列索引。

执行完后，就可以用下一句把id规整好。

update dupo set id=rownum where 1=1;

以上实验记录：

SQL> create table dupo(
  2  id int,
  3  name nvarchar2(20),
  4  create_time timestamp default sysdate,
  5  primary key(id));

表已创建。

SQL> insert into dupo(id,name) values(1,'andy');

已创建 1 行。

SQL> insert into dupo(id,name) values(2,'andy');

已创建 1 行。

SQL> insert into dupo(id,name) values(3,'andy');

已创建 1 行。

SQL> insert into dupo(id,name) values(4,'Bill');

已创建 1 行。

SQL> insert into dupo(id,name) values(5,'Bill');

已创建 1 行。

SQL> insert into dupo(id,name) values(6,'Bill');

已创建 1 行。

SQL> insert into dupo(id,name) values(7,'Bill');

已创建 1 行。

SQL> insert into dupo(id,name) values(8,'Cindy');

已创建 1 行。

SQL> insert into dupo(id,name) values(9,'Cindy');

已创建 1 行。

SQL> insert into dupo(id,name) values(10,'Douglas');

已创建 1 行。

SQL> commit;

提交完成。

SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id<a.id);

已删除6行。


SQL> select id,name from dupo;

        ID NAME
---------- ----------------------------------------
         1 andy
         4 Bill
         8 Cindy
        10 Douglas

SQL> rollback;

回退已完成。

SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id>a.id);

已删除6行。

SQL> select id,name from dupo;

        ID NAME
---------- ----------------------------------------
         3 andy
         7 Bill
         9 Cindy
        10 Douglas

SQL> rollback;

回退已完成。

SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid<a.rowid);

已删除6行。

SQL> select id,name from dupo;

        ID NAME
---------- ----------------------------------------
         1 andy
         4 Bill
         8 Cindy
        10 Douglas

SQL> rollback;

回退已完成。

SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid>a.rowid);

已删除6行。

SQL> select id,name from dupo;

        ID NAME
---------- ----------------------------------------
         3 andy
         7 Bill
         9 Cindy
        10 Douglas

SQL> rollback;

回退已完成。

SQL> select id,name from dupo;

        ID NAME
---------- ----------------------------------------
         1 andy
         2 andy
         3 andy
         4 Bill
         5 Bill
         6 Bill
         7 Bill
         8 Cindy
         9 Cindy
        10 Douglas

已选择10行。

SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid>a.rowid);

已删除6行。

SQL> update dupo set id=rownum where 1=1;

已更新4行。

SQL> select id,name from dupo;

        ID NAME
---------- ----------------------------------------
         1 andy
         2 Bill
         3 Cindy
         4 Douglas

SQL>