Oracle 重複行を検索して削除する

投稿者:sasaki 投稿日時:木, 2008-04-10 11:19

重複行を削除したいケースがたまにある。

  • 取り込んだマスターデータに重複があった
  • インポートを2回行って、二重登録してしまった

サンプル: 住所録テーブル

ID   名前   電話       住所

1   阿部   03-1234-1234  東京

2   井上   03-5678-5678  東京

3   阿部   03-1234-1234

4   井上   03-5678-5678 

 

今回は 名前、電話 が重複している3行目と4行めを削除することが目的。

ID番号を用いていない場合は、Oracleが内部で使用している行番号ROWIDを利用する。 

 

■重複行を検索して、何行くらいあるかを調べる

数千行のデータの中から、何行重複しているかを調べる。

SQL> SELECT 名前, 電話, count(*)
2 FROM 住所録
3 GROUP BY 名前, 電話
4 HAVING count(*) > 1 ; 

名前、電話 が重複している行がリストアップされる。

(住所 は無視するというケース) 

 

重複が1行や2行であれば手動で削除し、数百行に及ぶ場合はSQL文で削除を実行するという目安になる。

 

 

■残したいデータを検索する

重複していない行、つまり残しておきたい行を検索する。

SQL> SELECT MIN( id )
2 FROM 住所録
3 GROUP BY 名前, 電話 ; 

名前、電話 が重複せず1件だけ存在しているときは、その行のIDがリストアップされる。

名前、電話 が重複して3件存在している場合は、一番小さいID番号がリストアップされる。

よって、残しておきたい行の、ID番号がリストアップされる。

 

idが存在しないときは、ROWIDを使う。

SQL> SELECT MIN( rowid )
2 FROM 住所録
3 GROUP BY 名前, 電話 ;

 

 

■残したいデータ以外を削除する

上記で検索した、「残したいデータ」以外を削除する。

SQL> DELETE FROM 住所録
2 WHERE id NOT IN
3 (
4 SELECT MIN( id )
5 FROM 住所録
6 GROUP BY 名前, 電話
7 ) ; 

 これで 名前、電話 が重複したデータを削除できる。

 

idが存在しないときは、ROWIDを使う。

SQL> DELETE FROM 住所録
2 WHERE rowid NOT IN
3 (
4 SELECT MIN( rowid )
5 FROM 住所録
6 GROUP BY 名前, 電話
7 ) ; 

 

 

■重複行を削除する別の方法

名前、電話 が重複しているデータを検索して表示する。

SQL> SELECT rowid, 名前, 電話 FROM 住所録 A
2 WHERE A.rowid > (
3 SELECT MIN( B.rowid )
4 FROM 住所録 B
5 WHERE B.名前 = A.名前
6 AND B.住所 = A.住所
7 ) ;

 2行目の A.rowid > は、

A.rowid > B.rowid という真偽を判定している。

 

名前、電話 が重複せず1件だけ存在しているときは、A.rowid > B.rowid は成り立たないのでリストアップされない。

名前、電話 が重複して3件存在している場合は、A.rowid > B.rowid を処理し一番小さいROWID以外の2件がリストアップされる。

 

確認したら、削除する。

SQL> DELETE FROM 住所録 A
2 WHERE A.rowid > (
3 SELECT MIN( B.rowid )
4 FROM 住所録 B
5 WHERE B.名前 = A.名前
6 AND B.住所 = A.住所
7 ) ;