表中重复记录

查找表中重复记录

delete from test1 where rowid in
(select rid from
(select rowid rid,row_number() over(partition by id order by rowid) rn
from test1)
where rn <> 1 );

create table test2 as
select id,name from
(select test1.*,row_number() over(partition by id order by rowid) rn from test1)
where rn = 1;

truncate table test1;

insert into test1 select * from test2;

drop table test2;

select id from t a
where rowid !=(select min(rowid) from t b
where a.id=b.id)

1,@?/rdbms/admin/utlexcpt1

2, 创建唯一性于DISABLE NOVALIDATE

3,SQL> ALTER TABLE table_name
ENABLE VALIDATE CONSTRAINT unique_constraint
EXCEPTIONS INTO system.exceptions;
4,SELECT rowid, column FROM table_name
WHERE ROWID in (SELECT row_id
FROM exceptions)

SQL> select * from test1;

ID NAME

1 wwww
1 wwww
1 wwww
1 wwww
1 wwww
2 dddddd
2 dddddd
2 dddddd
2 dddddd
2 dddddd
3 sdfsdf
3 sdfsdf
3 sdfsdf
3 sdfsdf

已选择14行。

SQL> alter table test1
2 add constraint u_test1 unique (id,name) disable novalidate;

表已更改。

SQL> alter table test1
2 enable validate constraint u_test1 exceptions into exceptions;
alter table test1
*
第 1 行出现错误:
ORA-02299: cannot validate (SCOTT.U_TEST1) - duplicate keys found

好接下来我们来看看exceptions中的数据:

SQL> select * from test1
2 where rowid in (select row_id from exceptions);

ID NAME
---------- ----------------------------------------
1 wwww
1 wwww
1 wwww
1 wwww
1 wwww
2 dddddd
2 dddddd
2 dddddd
2 dddddd
2 dddddd
3 sdfsdf
3 sdfsdf
3 sdfsdf
3 sdfsdf

已选择14行。

所有重复的数据,在exceptions中都有一条记录,直接delete:

delete from test1
where rowid in (select rowid from exceptions);

这样显然不对,重复的数据我们得保留一行阿

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472923/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472923/