SQL Serverで重複行を削除する方法｜ROW_NUMBERで残す1行を決め、件数を確かめてから消す

SQL Serverのテーブルから重複行を消すとき、先に決めておくことが2つあります。どの列が同じなら重複とみなすか、そして重複した行のうちどの1行を残すかです。DELETE文の書き方よりも、こちらのほうが大事です。ここを決めないまま消すと、残すつもりだった新しい行が消えたり、関係のない行まで巻き込んだりします。

この記事では、ROW_NUMBER() で重複グループごとに番号を振り、2番目以降の行だけを消します。進め方は次のとおりです。

1. 重複とみなすキー列を決める
2. 残す1行の基準（並び順）を決める
3. SELECTで削除対象と件数を確かめる
4. 削除対象を別テーブルに退避する
5. トランザクションの中でDELETEし、結果を確かめてからCOMMITする

例として、同じメールアドレスの顧客が何件も登録されてしまった dbo.Customers テーブルを使います。

テーブル名や列名は、自分の環境のものに置き換えてください。

作業前に確認しておくこと

• 本番の前に、テスト環境か同じ構造のコピーで一度最後まで流す
• データベースのバックアップがあるか、復元の手順を誰が知っているかを確かめる
• DELETE権限があるか、作業してよい時間帯か（ほかの処理が同じテーブルを更新していないか）
• 削除する行がほかのテーブルから外部キーで参照されていないか、DELETEトリガーがないか

外部キーで参照しているテーブルは、次のクエリで調べられます。

SELECT name, OBJECT_NAME(parent_object_id) AS referencing_table
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('dbo.Customers');

トリガーは、SSMSのオブジェクト エクスプローラーでテーブルを展開し、「トリガー」フォルダーを見れば確認できます。

手順1：重複とみなすキー列を決めて、重複の状況を見る

まず、どの列（または列の組み合わせ）が同じなら同じデータとみなすかを決めます。Emailだけで判定するのか、Email＋Nameで判定するのかによって、消える行は変わります。

キーが決まったら、重複しているキーとその件数を出します。

SELECT Email, COUNT(*) AS cnt
FROM dbo.Customers
GROUP BY Email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

このときに、次の2点も見ておきます。

• NULLの扱い：PARTITION BY は、キーがNULLの行どうしを1つのグループにまとめます。上のクエリでEmailがNULLの行が出てきて、それを重複として消したくない場合は、以降のクエリに WHERE Email IS NOT NULL を付けて対象から外します（この記事の例では外しています）。
• 大文字・小文字や全角・半角の扱い：同じ文字とみなすかどうかは、列の照合順序（collation）で決まります。

SELECT name, collation_name
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.Customers');

照合順序の名前に _CI が含まれていれば大文字と小文字を区別せず、_CS なら区別します。_WS は全角と半角を、_KS はひらがなとカタカナを区別する設定で、これらが名前に含まれていなければ区別しません。

「株式会社」と「(株)」のような表記の違いは、この方法では同じものとして扱われません。表記をそろえる名寄せは削除とは別の作業として、先に済ませておきます。

手順2：残す1行の基準を決める

ROW_NUMBER() は、PARTITION BY で分けたグループごとに、ORDER BY の順で1、2、3…と番号を振ります。1番の行を残して2番以降を消すので、ORDER BYの書き方がそのまま「残す行の基準」になります。

注意すること：

• 並び替えに使う列の値が同じ行があると、どちらが1番になるかは決まっていません。最後に主キーのような一意の列を付け足して、何度実行しても同じ行が残るようにします。
• SQL ServerではNULLが最も小さい値として扱われ、昇順なら先頭、降順なら末尾に来ます。UpdatedAtにNULLがある場合、UpdatedAt DESC ではNULLの行が後ろに回るので残りにくくなります。
• 主キーまで含めてすべての列が同じ行だけを消すのであれば、どれを残しても結果は変わらないため ORDER BY (SELECT NULL) と書く方法もあります。列の値が異なる行が混ざっている場合は使わないでください。

手順3：SELECTで削除対象と件数を確かめる

CTE（WITH句）で番号の列 rn を付け、最初はSELECTだけを実行します。

WITH d AS (
    SELECT
        Id, Email, Name, UpdatedAt,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY UpdatedAt DESC, Id DESC
        ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
SELECT *
FROM d
WHERE Email IN (SELECT Email FROM d WHERE rn > 1)
ORDER BY Email, rn;

重複したグループごとに、rn = 1（残る行）と rn が2以上の行（消える行）が並んで表示されます。いくつかのグループを実際に見て、残したい行が1番になっていることを確かめます。

次に件数を確かめます。

WITH d AS (
    SELECT
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY UpdatedAt DESC, Id DESC
        ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
SELECT
    COUNT(*) AS target_rows,
    SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END) AS delete_rows,
    SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) AS keep_rows
FROM d;

数字が合わない、または思ったより多い場合は、キー列やWHERE条件に戻って見直します。

手順4：削除する行を別テーブルに退避する

DELETEの前に、消える行をまるごと別のテーブルにコピーしておきます。あとで「この行は残すべきだった」と分かっても、ここから戻せます。

WITH d AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY UpdatedAt DESC, Id DESC
        ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
SELECT *
INTO dbo.Customers_dup_20261006
FROM d
WHERE rn > 1;

退避テーブルの件数が、手順3のdelete_rowsと同じか確認します。テーブル名に作業日を入れておくと、何の作業で作ったものかが後から分かります。不要になったら運用ルールに沿って削除します。

手順5：トランザクションの中でDELETEする

SSMSで、次のクエリを実行します。この時点ではCOMMITを書かないでください。

BEGIN TRANSACTION;

WITH d AS (
    SELECT
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY UpdatedAt DESC, Id DESC
        ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
DELETE FROM d
WHERE rn > 1;

SELECT @@ROWCOUNT AS deleted_rows;

• CTEが参照しているテーブルが1つだけなら、CTEに対してDELETEすると元のテーブルの行が消えます。
• @@ROWCOUNT が返すのは直前の文で処理された件数なので、DELETEのすぐ後に書きます。
• WITHの1つ前の文はセミコロンで終わっている必要があります。BEGIN TRANSACTION; の「;」は消さないでください。

deleted_rowsが手順3のdelete_rowsと同じであれば、トランザクションを閉じる前に結果を確かめます。

-- 重複が残っていないか（0行になるはず）
SELECT Email, COUNT(*) AS cnt
FROM dbo.Customers
WHERE Email IS NOT NULL
GROUP BY Email
HAVING COUNT(*) > 1;

-- 残った件数
SELECT COUNT(*) AS total_rows FROM dbo.Customers;

問題がなければ確定し、想定と違えば取り消します。

COMMIT TRANSACTION;      -- 問題がない場合
-- ROLLBACK TRANSACTION; -- 想定と違う場合

トランザクションを開いたままにしておくと、同じテーブルを使うほかの処理が待たされることがあります。確認は手早く済ませ、COMMITかROLLBACKで必ず閉じてください。

件数の確認をスクリプトに組み込む場合

ジョブなど、途中で人が確認できない形で実行するときは、件数が想定と違ったら自動でロールバックするように書いておきます。

SET XACT_ABORT ON;

DECLARE @expected int, @deleted int;

WITH d AS (
    SELECT ROW_NUMBER() OVER (
        PARTITION BY Email ORDER BY UpdatedAt DESC, Id DESC
    ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
SELECT @expected = COUNT(*) FROM d WHERE rn > 1;

BEGIN TRANSACTION;

WITH d AS (
    SELECT ROW_NUMBER() OVER (
        PARTITION BY Email ORDER BY UpdatedAt DESC, Id DESC
    ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
DELETE FROM d WHERE rn > 1;

SET @deleted = @@ROWCOUNT;

IF @deleted = @expected
    COMMIT TRANSACTION;
ELSE
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, N'削除件数が想定と一致しないためロールバックしました。', 1;
END;

件数を数えてからDELETEするまでの間に、ほかの処理が行を追加・更新すると件数がずれてロールバックになります。そうなった場合は、ほかの処理が止まっている時間帯に実行してください。

削除できない・結果がおかしいときの確認

実行前チェックリスト

• 重複とみなすキー列を決めた
• キーがNULLの行を対象に含めるかどうか決めた
• ORDER BYの最後に一意の列を入れ、残る行を実際に見て確かめた
• 削除する件数（delete_rows）を控えた
• 削除対象を退避テーブルにコピーし、件数が一致した
• BEGIN TRANSACTIONの中でDELETEし、@@ROWCOUNTが想定と一致した
• 重複が0件になったことを確かめてからCOMMITした

テーブルの行は消さず、SELECTの結果から重複を除いて表示したいだけなら、DELETEではなくクエリ側で処理する方法を使ってください。