重複するレコードを確認して削除するSQLを紹介します。

方針

重複を定義する

はじめに、どのカラムがすべて同じなら「重複」とみなすのかを明確にします。 (例：name、email、birthdateがすべて同じなら重複とみなす。など)

主キー（idなど）は通常重複していないので、重複チェックの対象から除きます。

残すレコードの基準を決める

重複レコードの中でどれを「残す」かを決めます。シナリオによっては、残さないという方針もあります。

よくある基準では以下があります。

• idが最小のものを残す
• created_atが最も古いものを残す

重複を検出して、削除する対象を特定する

重複レコードを検出して削除します。

例

以下のテーブルを作成します。

Products テーブル

id	name	price	category
1	商品A	1000	食品
2	商品A	1000	食品
3	商品A	1000	食品
4	商品B	2000	家電
5	商品B	2000	家電
6	商品C	1500	衣料
7	商品D	3000	食品
8	商品E	1000	食品
9	商品A	1000	飲料

INSERT INTO [dbo].[ProductsH] ([name], [price], [category]) VALUES
(N'商品A', 1000, N'食品'),
(N'商品A', 1000, N'食品'),
(N'商品A', 1000, N'食品'),
(N'商品B', 2000, N'家電'),
(N'商品B', 2000, N'家電'),
(N'商品C', 1500, N'衣料'),
(N'商品D', 3000, N'食品'),
(N'商品E', 1000, N'食品'),
(N'商品A', 1000, N'飲料');

重複レコードの確認

以下のSQLを実行して、重複しているレコードを確認します。name, price, category の値が一致した場合に重複レコードとみなします。

SELECT name, price, category, COUNT(*) AS duplicate_count
FROM [dbo].[Products] GROUP BY [name], [price], [category] HAVING COUNT(*) > 1

結果は以下です。

#[pre class="brush:sql"]]

name	price	category	duplicate_count
商品A	1000	食品	3
商品B	2000	家電	2

重複レコードの削除

以下のSQLを実行して、重複しているレコードを削除します。
name, price, category の値が一致した場合に重複レコードとみなします。

WITH Duplicates AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY [name], [price], [category]
            ORDER BY id
        ) AS rn
    FROM dbo.Products
)
DELETE FROM dbo.Products
WHERE id IN (
    SELECT id FROM Duplicates WHERE rn > 1
);

解説

以下では、PARTITION BY で重複するレコードをIDの小さい順にrn列に1から値を割り当てます。

    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY [name], [price], [category]
            ORDER BY id
        ) AS rn
    FROM dbo.Products

上記のSQLの実行結果は以下になります。

id	rn
9	1
1	1
2	2
3	3
4	1
5	2
6	1
7	1
8	1

rnが2以上のレコードを削除すると重複を除去できます。

WITH Duplicatesは「共通テーブル式」（CTE: Common Table Expression）で、このSQL内で利用できる一時的な名前付きのクエリ結果です。 共通テーブル式についてはこちらの記事を参照してください。
削除するIDを先の結果のDuplicatesの共通テーブル式から参照して、レコードを削除します。

DELETE FROM dbo.Products
WHERE id IN (
    SELECT id FROM Duplicates WHERE rn > 1
);

実行結果

実行結果は以下となります。重複していたレコードを削除できました。

id	name	price	category
1	商品A	1000	食品
4	商品B	2000	家電
6	商品C	1500	衣料
7	商品D	3000	食品
8	商品E	1000	食品
9	商品A	1000	飲料