SQLで重複データを抽出・削除する方法｜1件だけ残す書き方

SQLで重複しているデータを探すには、GROUP BY で重複を判定したい列をまとめ、HAVING COUNT(*) > 1 で2件以上あるものを絞り込みます。重複を1件だけ残して削除するには、ROW_NUMBER() で各グループに番号を振り、2番目以降を削除する方法が、多くのデータベースで使える確実な書き方です。

ただし、削除は取り消しのきかない操作です。この記事では、重複の抽出から、削除する前に対象を確認する手順、データベース別の削除の書き方、MySQLのエラー1093やNULLを含む重複といったつまずきやすい点、そして重複を二度と作らないための対策までを、PostgreSQLで実際に実行した結果とあわせて解説します。

例に使うテーブル

会員テーブルに、同じ会員が重複して登録されてしまった状態を例にします。

a@example.com が3件、b@example.com が2件重複しています。id（主キー）が小さいものを1件残し、残りを削除することを目標にします。

重複しているデータを抽出する

重複している値と件数を調べる

SELECT email, COUNT(*)
FROM members
GROUP BY email
HAVING COUNT(*) > 1;

     email     | count
---------------+-------
 a@example.com |     3
 b@example.com |     2

複数の列の組み合わせで重複を判定する場合は、GROUP BYに列を並べます。

SELECT email, name, tel, COUNT(*)
FROM members
GROUP BY email, name, tel
HAVING COUNT(*) > 1;

重複している行を、すべての列ごと表示する

GROUP BYでは、重複している行の他の列（idなど）が見えません。ウィンドウ関数のCOUNTを使うと、行をまとめずに件数を付けられます。

SELECT *
FROM (
  SELECT m.*, COUNT(*) OVER (PARTITION BY email) AS cnt
  FROM members m
) x
WHERE cnt > 1
ORDER BY email, id;

 id |     email     | name | tel | cnt
----+---------------+------+-----+-----
  1 | a@example.com | 山田 | 090 |   3
  2 | a@example.com | 山田 | 090 |   3
  6 | a@example.com | 山田 | 090 |   3
  3 | b@example.com | 佐藤 |     |   2
  4 | b@example.com | 佐藤 |     |   2

ウィンドウ関数が使えない古いデータベースでは、サブクエリで重複している値を求めて絞り込みます。

SELECT * FROM members
WHERE email IN (
  SELECT email FROM members GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email, id;

1件だけ残して削除する

手順1：必ず、削除する対象を先に確認する

削除のSQLを書く前に、同じ条件でSELECTして、消える行を目で確認します。

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email, name, tel ORDER BY id) AS rn
  FROM members
)
SELECT id FROM ranked WHERE rn > 1;

 id
----
  2
  4
  6

PARTITION BY に重複を判定する列、ORDER BY にどれを残すかの順番を指定します。ORDER BY id なら最も古い行（idが最小）が1番になって残り、ORDER BY id DESC や ORDER BY updated_at DESC なら、最も新しい行が残ります。

手順2：トランザクションの中で削除し、結果を確認する

BEGIN;

DELETE FROM members
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email, name, tel ORDER BY id) AS rn
    FROM members
  ) r
  WHERE rn > 1
);

SELECT * FROM members ORDER BY id;   -- 結果を確認

COMMIT;     -- 問題なければ確定
-- ROLLBACK; -- 想定と違えば取り消す

 id |     email     | name | tel
----+---------------+------+-----
  1 | a@example.com | 山田 | 090
  3 | b@example.com | 佐藤 |
  5 | c@example.com | 鈴木 | 080

BEGINで始めておけば、COMMITするまでは取り消せます。本番のデータで実行する場合は、事前にバックアップを取るか、対象のテーブルをコピーしておくと安心です。

CREATE TABLE members_backup AS SELECT * FROM members;

データベース別の削除の書き方

ROW_NUMBER() を使う方法は、PostgreSQL・MySQL 8.0以降・SQL Server・Oracleで使えます。それぞれのデータベースには、より簡潔な書き方もあります。

PostgreSQL：DELETE ... USING（自己結合）

DELETE FROM members a
USING members b
WHERE a.email = b.email
  AND a.id > b.id;       -- idが大きい方を削除（最小のidが残る）

MySQL：DELETE ... JOIN（自己結合）

DELETE a
FROM members a
JOIN members b
  ON a.email = b.email
 AND a.id > b.id;

SQL Server：CTEから直接削除

WITH ranked AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM members
)
DELETE FROM ranked WHERE rn > 1;

SQL Serverでは、CTE（WITH句）に対して直接DELETEでき、対応する元のテーブルの行が削除されます。

Oracle：ROWIDを使う

DELETE FROM members a
WHERE a.ROWID > (
  SELECT MIN(b.ROWID) FROM members b WHERE b.email = a.email
);

MySQLのエラー1093の回避

MySQLで、削除するテーブルと同じテーブルをサブクエリで参照すると、次のエラーになります。

DELETE FROM members
WHERE id NOT IN (SELECT MIN(id) FROM members GROUP BY email);
-- ERROR 1093 (HY000): You can't specify target table 'members' for update in FROM clause

回避するには、サブクエリをもう1段の派生テーブルで包むか、前述の DELETE ... JOIN を使います。

DELETE FROM members
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id FROM members GROUP BY email
  ) AS keep_ids
);

派生テーブル（AS keep_ids）の結果は、一時的な表として先に作られるため、削除の対象と同じテーブルを参照していても実行できます。

落とし穴：NULLを含む重複が消えない

重複の判定に使う列に NULL が含まれる場合、自己結合（a.tel = b.tel）の方法では、その行が重複として検出されません。

-- 重複ペアの数を数える
SELECT COUNT(*) FROM members a JOIN members b
  ON a.email = b.email AND a.tel = b.tel AND a.id < b.id;
-- 結果：3（佐藤さんの重複が数えられていない）

SQLでは、NULL = NULL は「真」ではなく「不明」と扱われるため、等号の条件に一致しないのです。NULLどうしも同じ値として比較するには、次のように書きます。

-- PostgreSQL
SELECT COUNT(*) FROM members a JOIN members b
  ON a.email = b.email AND a.tel IS NOT DISTINCT FROM b.tel AND a.id < b.id;
-- 結果：4（佐藤さんの重複も数えられる）

一方、GROUP BYとPARTITION BYでは、NULLは同じグループとして扱われます。そのため、ROW_NUMBER() を使う方法なら、NULLを含む重複も正しく削除できます。

主キーがないテーブルの場合

主キー（id）がなく、すべての列がまったく同じ行が重複している場合は、「どちらの行か」を区別する手がかりがありません。データベースが内部で持っている行の識別子を使います。

-- PostgreSQL：主キーのないテーブルで、完全に同じ行を1件だけ残す
DELETE FROM logs a
USING logs b
WHERE a.message = b.message
  AND a.ctid > b.ctid;

MySQLで主キーがない場合は、重複をなくした新しいテーブルを作って入れ替える方法が確実です。

CREATE TABLE logs_new AS SELECT DISTINCT * FROM logs;
RENAME TABLE logs TO logs_old, logs_new TO logs;

入れ替えた後は、インデックスや制約を作り直す必要があるので注意します。

大量のデータを削除するとき

数百万件の重複を一度に削除すると、ロックが長時間続き、他の処理が止まることがあります。

• 対象のidを一時テーブルに保存し、1,000件〜1万件ずつに分けて削除する
• 業務時間外に実行する
• 削除後、統計情報を更新する（PostgreSQLなら ANALYZE）

重複を二度と作らないための対策

削除しても、原因をそのままにすると、また重複が生まれます。

ユニーク制約を追加する

ALTER TABLE members ADD CONSTRAINT uq_members_email UNIQUE (email);

重複を削除してから制約を追加します（重複が残っていると追加できません）。

注意点として、多くのデータベースでは、ユニーク制約はNULLどうしの重複を許します。PostgreSQLでは、次の結果になります。

CREATE TABLE u (email text, tel text, UNIQUE (email, tel));
INSERT INTO u VALUES ('a', NULL), ('a', NULL);   -- エラーにならず、2件とも登録される

PostgreSQL 15以降では UNIQUE NULLS NOT DISTINCT で、NULLも重複として扱えます。NULLになり得る列を組み合わせてユニークにしたい場合は、列をNOT NULLにすることも検討します。

登録時に重複を防ぐ書き方

重複が生まれた原因を確認する

• 登録ボタンの二重送信を防げていない
• 画面とAPIで、同じチェックが二重に行われていない（チェックと登録の間に、別の処理が同じデータを登録する）
• データの取り込み処理を、同じファイルで2回実行した

アプリケーションでの事前チェック（SELECTしてから INSERT）は、同時に実行されると両方が通ってしまいます。最終的な防御は、データベースのユニーク制約に任せるのが確実です。

よくある質問

Q. DISTINCTで重複を削除できますか？ A. DISTINCTは、SELECTの結果から重複を除いて表示するだけで、テーブルのデータは削除しません。テーブルから重複を消すには、DELETE文が必要です。

Q. 最新のデータを残したい場合はどうすればいいですか？ A. ROW_NUMBER()の ORDER BY を、ORDER BY updated_at DESC のように新しい順にします。自己結合の場合は、a.id < b.id のように不等号の向きを逆にすると、idが大きい行が残ります。

Q. 大文字と小文字だけが違うメールアドレスも重複として扱いたいです。 A. 判定の列を lower(email) のように揃えてから、GROUP BYやPARTITION BYに指定します。登録時にも小文字に揃えて保存しておくと、後の重複を防げます。

Q. 重複を削除したら、他のテーブルの参照先がなくなりました。 A. 削除する行を、他のテーブル（注文など）が外部キーで参照している場合は、先に参照先を残す行に付け替えてから削除します。外部キー制約があれば、削除はエラーになって止まります。

まとめ

• 重複の抽出は GROUP BY ... HAVING COUNT(*) > 1、行ごと見るなら COUNT(*) OVER (PARTITION BY ...)
• 1件残して削除するなら ROW_NUMBER() で2番目以降を削除。ORDER BYで残す行を選ぶ
• 削除の前にSELECTで対象を確認し、トランザクションの中で実行する
• MySQLのエラー1093は、派生テーブルで包むか、DELETE ... JOINで回避する
• 自己結合ではNULLを含む重複が消えない。IS NOT DISTINCT FROM や <=> を使う
• 再発防止はユニーク制約。ただしNULLどうしの重複は許される点に注意する

株式会社RJCでは、データを安全に扱うための手順を、「なぜそうするのか」とあわせて仲間と共有しています。こうした知識を共有しながら、一緒に成長を楽しんでいます。

株式会社RJC ― SI事業・SES事業・AI駆動開発。未経験からエンジニアに育てるのが得意な会社です。