WEB开发网
开发学院数据库Oracle oracle快速删除重复的记录 阅读

oracle快速删除重复的记录

 2007-05-13 12:31:58 来源:WEB开发网   
核心提示: 具体步骤如下:SQL>@?/rdbms/admin/utlexcpt.sqlTable created.SQL>desc exceptions Name Null? TypeROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(

具体步骤如下:

  SQL>@?/rdbms/admin/utlexcpt.sql
  Table created.
  SQL>desc exceptions
   Name Null? Type
  ROW_ID ROWID
   OWNER VARCHAR2(30)
   TABLE_NAME VARCHAR2(30)
   CONSTRAINT VARCHAR2(30)
  SQL>alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions;
   *
   ERROR at line 1:
   ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found
  SQL>create table dups as select * from cz where rowid in (select row_id from exceptions);
  Table created.
  SQL>select * from dups;
  C1 C10 C20
  1 2 dsf
   1 2 dsf
   1 2 dsf
   1 2 dsf
   2 3 che
   1 2 dsf
   1 2 dsf
   1 2 dsf
   1 2 dsf
   2 3 che
   2 3 che
   2 3 che
   2 3 che
   3 4 dff
   3 4 dff
   3 4 dff

16 rows selected.

  SQL>select row_id from exceptions;
  ROW_ID
   AAAHD/AAIAAAADSAAA
   AAAHD/AAIAAAADSAAB
   AAAHD/AAIAAAADSAAC
   AAAHD/AAIAAAADSAAF
   AAAHD/AAIAAAADSAAH
   AAAHD/AAIAAAADSAAI
   AAAHD/AAIAAAADSAAG
   AAAHD/AAIAAAADSAAD
   AAAHD/AAIAAAADSAAE
   AAAHD/AAIAAAADSAAJ
   AAAHD/AAIAAAADSAAK
   AAAHD/AAIAAAADSAAL
   AAAHD/AAIAAAADSAAM
   AAAHD/AAIAAAADSAAN
   AAAHD/AAIAAAADSAAO
   AAAHD/AAIAAAADSAAP

16 rows selected.

  SQL>delete from cz where rowid in ( select row_id from exceptions);

16 rows deleted.

  SQL>insert into cz select distinct * from dups;
  3 rows created.
  SQL>select *from cz;
  C1 C10 C20
  1 2 dsf
   2 3 che
   3 4 dff
   4 5 err
   5 3 dar
   6 1 wee
   7 2 zxc
  7 rows selected.

从结果里可以看到重复记录已经删除。

上一页  1 2 3 

Tags:Oracle 快速 删除

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接