Oracle® 非公式 SQL, PL/SQL & DBA's ガイドサイト

重複レコードの選別と削除

インポートの失敗や、テストプログラムの不具合によって、プライマリキー のないテーブルに大量の重複データを生成してしまうことがある。
そのようなとき、重複レコードを手作業での削除するとミスや漏れが発生しやすい。

サンプルとして、以下のテーブルに間違って DUPL_COL、LOG_DATA の内容がまったく同じデータを作成してしまった。 DUPL_COL と LOG_DATA が同じものは、1レコードだけを残し削除したい場合を考える。

CREATE TABLE RIVUS.DUPL_TEST
(
	DUPL_COL	VARCHAR2(10),
	LOG_DATA	VARCHAR2(250),
	CREATED_ON	DATE DEFAULT SYSDATE
);

重複レコードを無作為に削除する SQL

まったく同じデータを作成しただけであって、どれを消しても問題ない場合には、比較的簡単に行える。

• 削除する SQL (削除前には必ずバックアップ)

/*****
 SQLの内容
 DELETE 〜 WHERE ROWID > (...)
   └ d2 から d1の dupl_col, log_data が一致する一番小さな ROWIDを抽出
 *****/
DELETE FROM dupl_test d1
WHERE ROWID > (
	SELECT MIN(ROWID) FROM dupl_test d2
	WHERE
		d1.dupl_col = d2.dupl_col
		AND d1.log_data = d2.log_data
)

削除するレコードと残すレコードを抽出する SQL

削除履歴を残すならば CREATE TABLE xxx_DEL_LIST_date AS SELECT 〜 のようにしておくと証拠が残せるので作業前にはバックアップだけでなく更新履歴をしばらく残しておくと後々のトラブル時の影響範囲、原因調査や回復作業にもすばやく対応できる。
dupl_test から dupl_col, log_data が重複するデータを削除する。

• 削除対象と残すレコードを一覧表示する SQL(重複していないレコードは抽出しない)

-- CREATE TABLE dup_test_del_list AS
SELECT '#REMAIN' STATUS, d1.*, ROWID 
FROM dupl_test d1
WHERE ROWID = (
	-- ここで重複しているもので、残すレコードを抽出(ROWIDが一番小さなもの)
	SELECT MIN(ROWID) FROM dupl_test d2
	WHERE
		d1.dupl_col = d2.dupl_col
		AND D1.log_data = d2.log_data
	HAVING COUNT(*) > 1		-- 重複しているレコードグループの選別
)
UNION ALL 
SELECT 'DELETE' STATUS, d1.*, ROWID 
FROM dupl_test d1
WHERE ROWID > (
	-- ここで削除するレコードを抽出
	SELECT MIN(ROWID) FROM dupl_test d2
	WHERE
		d1.dupl_col = d2.dupl_col
		AND d1.log_data = d2.log_data
)
ORDER BY 2,3,1

関連事項

• DELETE
• UNION / UNION ALL
• カラム・レコード単位で連続する値を省略・削除する方法