如何刪除在 Table 內重複的資料列 ?

有時候表格內發生了重複的資料列，造成 PK 或者 Unique key 無法建立，在大量的表格內要找出發生違反 constraint 的 data row ，可以使用下列的程序發現，再給予刪除後即可以重新建立 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>$?/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 ENAME

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

1 2 3 tom

1 2 5 tom

1 7 5 tom

7 7 5 tom

SQL> commit;

確認完成 .

---

提高刪除速度的方法 ( 小技巧 )

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