Oracle Databaseで、同じ値の組み合わせを持つ行が重複して登録されてしまい、各グループから1件だけ残して削除したいことがあります。
残す行に業務上の優先順位がない場合は、ROWID と GROUP BY、MIN(ROWID) を組み合わせることで、重複ごとに任意の1件を残して余分な行を削除できます。
この記事では、customer_code と product_code の2列を重複判定条件にした例で、実際に使用できるDELETE文と削除前後の結果を示します。
重複ごとに1件だけ残して削除するSQL
重複判定に使う列が customer_code と product_code の場合は、次のSQLで各組み合わせにつき1件だけ残せます。
DELETE FROM nexiblog_duplicate_cleanup
WHERE ROWID NOT IN (
SELECT MIN(ROWID)
FROM nexiblog_duplicate_cleanup
GROUP BY customer_code, product_code
);サブクエリでは、customer_code と product_code の組み合わせごとに MIN(ROWID) を取得しています。これが各重複グループで残す1行です。
外側のDELETE文は、その ROWID に含まれない行を削除します。そのため、同じ2列の組み合わせが複数行あっても1行だけが残り、もともと1行しかない組み合わせはそのまま残ります。
実際のテーブルへ適用する場合は、テーブル名と GROUP BY に指定する列を、重複判定に使いたいものへ置き換えてください。
サンプルデータで削除前後を確認する
動きを確認するため、2列だけを持つテーブルに重複データを登録します。
CREATE TABLE nexiblog_duplicate_cleanup (
customer_code VARCHAR2(20) NOT NULL,
product_code VARCHAR2(20) NOT NULL
);
INSERT INTO nexiblog_duplicate_cleanup (customer_code, product_code) VALUES ('C001', 'P100');
INSERT INTO nexiblog_duplicate_cleanup (customer_code, product_code) VALUES ('C001', 'P100');
INSERT INTO nexiblog_duplicate_cleanup (customer_code, product_code) VALUES ('C001', 'P100');
INSERT INTO nexiblog_duplicate_cleanup (customer_code, product_code) VALUES ('C002', 'P200');
INSERT INTO nexiblog_duplicate_cleanup (customer_code, product_code) VALUES ('C002', 'P200');
INSERT INTO nexiblog_duplicate_cleanup (customer_code, product_code) VALUES ('C003', 'P300');
COMMIT;customer_code と product_code ごとの件数は、次のSQLで確認できます。
SELECT customer_code, product_code, COUNT(*) AS row_count
FROM nexiblog_duplicate_cleanup
GROUP BY customer_code, product_code
ORDER BY customer_code, product_code;削除前は次の状態です。
C001 P100 3
C002 P200 2
C003 P300 1C001/P100 が3件、C002/P200 が2件重複しています。C003/P300 は1件だけなので重複していません。
ここで先ほどのDELETE文を実行すると、3行が削除されました。削除後に同じ集計SQLを実行した結果は次のとおりです。
C001 P100 1
C002 P200 1
C003 P300 1重複していた2つの組み合わせはそれぞれ1件だけになり、重複していなかった C003/P300 も1件のまま残っています。
なぜ1件だけ残るのか
まず、次の部分で重複判定列ごとに残す ROWID を1つ選びます。
SELECT MIN(ROWID)
FROM nexiblog_duplicate_cleanup
GROUP BY customer_code, product_code例えば C001/P100 が3行あっても、MIN(ROWID) として返るのは1行分です。C003/P300 のようにもともと1行しかない組み合わせも、その1行の ROWID が返ります。
その結果を外側のDELETE文で除外します。
WHERE ROWID NOT IN ( ... )つまり、「各グループで残すと決めた ROWID 以外を削除する」という形です。これにより、重複数にかかわらず各組み合わせが1行になります。
残す行を指定したい場合には使わない
この方法で残るのは、各グループの MIN(ROWID) に該当する任意の1行です。
そのため、「最新日時の行を残す」「特定のIDを優先する」といった業務上の条件がある場合には、このSQLで残す行を決めないでください。この方法は、同じ値を持つ行のうちどれを残してもよい場合に向いています。
また、DELETE文を実行するとデータが削除されるため、実際のテーブルへ適用する際は、対象テーブルと GROUP BY に指定する重複判定列を確認してから実行してください。
まとめ
Oracleで重複データを1件だけ残して削除するなら、重複判定列で GROUP BY し、各グループの MIN(ROWID) 以外をDELETEします。
DELETE FROM テーブル名
WHERE ROWID NOT IN (
SELECT MIN(ROWID)
FROM テーブル名
GROUP BY 重複判定列1, 重複判定列2
);残す行に特別な優先順位がない重複データの整理であれば、この形で重複グループごとに1件だけ残せます。
