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

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

• 取り込んだマスターデータに重複があった
• インポートを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  ) ;