刪除在 Table 內重複的資料列

有時候表格內發生了重複的資料列，造成 PK 或者 Unique key 無法建立，在大量的表格內要找出發生違反 constraint 的 data ，可以使用下列的程序發現，再給予刪除後即可以重新建立 constraint 。



Example:

Table name: test

SQL> select * from test;

ID ID1 ID2

---------- ---------- ----------

1 2 3

1 2 5

1 7 5

1 7 5

7 7 5



你可以發現重複了一筆資料 (1,7,5) ，如果我們要在 (ID,ID1,ID2 上建立 PK OR UNIQUE CONSTRAINT ，必須先做個資料清除動作 )



1. 確認表格內是否有重複的資料列

SQL> select id,id1,id2,count(*) from test group by id,id1,id2 having count(*) > 1;

ID ID1 ID2 COUNT(*)

---------- ---------- ---------- ----------

1 7 5 2

(1,7,5) 出現了兩筆重複的資料



2 .保守起見，先建立一個 EXCEPTION TABLE 來儲存重複的 ROW

SQL>@[ORACLE HOME 目錄]\rdbms\admin\utlexcpt

SQL> @D:\ORACLE\ORA90\RDBMS\ADMIN\UTLEXCPT;

以建立表格

SQL> alter table test add constraint test_pk primary key (id ,id1,id2)

2 exceptions into exceptions;

alter table test add constraint test_pk primary key (id ,id1,id2)

*

ERROR 在行 1:

ORA-02437: 無法驗證 (SYS.TEST_PK) - 主索引鍵違規

SQL> select count(*) from exceptions;

COUNT(*)

----------

2

有兩筆重複資料列。



3 .直接刪除重複的資料列

SQL> delete from test where rowid not in (select min(rowid) from test group by id,id1,id2);

已刪除 1 個資料列 .

SQL> select * from test;

ID ID1 ID2

---------- ---------- ----------

1 2 3

1 2 5

1 7 5

7 7 5