SQL Server のテーブルから重複する行を削除する方法

適用対象: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Express EditionMicrosoft SQL Server 2005 Developer Edition

概要


SQL Server テーブルには、重複する行や一意でない主キーを含めることはできません。 簡潔にするために、この記事では主キーを "キー" または "PK" と呼ぶことがありますが、これは常に "主キー" を表します。 重複する PK はエンティティの整合性に違反するものであり、リレーショナル システムでは許可されません。 SQL Server には、インデックス、UNIQUE 制約、PRIMARY KEY 制約、トリガなど、エンティティの整合性を強制するためのさまざまなメカニズムがあります。

それにもかかわらず、異常な状況では、重複する主キーが発生する可能性があり、その場合は削除する必要があります。 これらの問題が発生する 1 つの方法は、SQL Server の外部の非リレーショナル データに重複する PK が存在し、PK の一意性が強制されていないときにデータがインポートされる場合です。 もう 1 つの方法は、各テーブルにエンティティの整合性を強制しないなど、データベース設計エラーを使用することです。

多くの場合、重複する PK は、一意のインデックスを作成しようとしたときに検出され、重複するキーが見つかった場合は中止されます。 このメッセージは次の通りです。

Msg 1505、レベル 16、状態 1 重複キーで一意のインデックスを作成します。
Microsoft SQL Server 2000 または SQL Server 2005 を使用する場合、次のエラー メッセージが表示されることがあります。
オブジェクト名 '%.*ls' および索引名 '%.*ls' に重複キーが見つかったため、Msg 1505、レベル 16、状態 1 CREATE UNIQUE INDEX が終了しました。 重複するキー値は %ls です。
この資料では、テーブルから重複する主キーを検索して削除する方法について説明します。 ただし、再発を防ぐために重複が発生するプロセスを詳しく調べる必要があります。

詳細情報


この例では、重複する PK 値を持つ次の表を使用します。 この表では、主キーは 2 つの列 (col1、col2) です。 2 つの行に重複する PK があるため、一意インデックスまたは 主キー制約を作成できません。 この手順では、重複を識別して削除する方法を示します。

create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
最初の手順では、どの行に重複する主キー値があるかを特定します。

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
テーブル内にある重複 PK 値の各セットについて 1 行が返されます。 この結果の最後の列は、特定の PK 値の重複の数です。

col1col2
112


重複する PK 値が数セットしかない場合は、これらを個別に手動で削除するのが最善の方法です。 例:

set rowcount 1
delete from t1
where col1=1 and col2=1
行数の値は、指定されたキー値の重複数を n-1 にする必要があります。 この例では、重複が 2 つあるため、行数は 1 に設定されています。 col1/col2 値は、上記の GROUP BY クエリ結果から取得されます。 GROUP BY クエリが複数の行を返す場合は、"SET ROWCOUNT" クエリをこれらの行ごとに 1 回実行する必要があります。 実行されるたびに、行数を n-1 に設定して、特定の PK 値の重複数を指定します。

行を削除する前に、行全体が重複していることを確認する必要があります。 可能性は低いとはいえ PK 値が重複している可能性がありますが、行全体は重複していません。 たとえば、社会保障番号を主キーにし、それぞれが一意の属性を持つ同じ番号を持つ 2 人の異なる人 (または行) を持つテーブルなどです。 このような場合、重複キーの誤動作によって、有効な一意のデータが行に配置される可能性もあります。 このデータは、データを削除する前に、調査と可能な調整のためにコピーして保存する必要があります。

テーブル内に重複する PK 値のセットが多数ある場合は、個別の削除に時間がかかりすぎる可能性があります。 この場合、次の手順を使用できます。

  1. まず、上記の GROUP BY クエリを実行して、重複する PK 値のセットの数と、各セットの重複の数を確認します。
  2. 保持テーブルに重複するキー値を選択します。 例:
    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
  3. 保持テーブルに重複する行を選択し、プロセス内の重複を除去します。 例:
    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
  4. この時点では、holddups テーブルは一意の PK を持つ必要がありますが、t1 に重複する PK があり、一意の行がある場合はそうではありません (上記の SSN の例のように)。 holddups の各キーが一意であり、重複するキーがなく、一意の行が存在しないことを確認します。 その場合は、ここで停止し、特定の重複キー値に対して保持する行を調整する必要があります。 たとえば、次のクエリを実行します。
    SELECT col1, col2, count(*)
    FROM holddups
    GROUP BY col1, col2
    各行に対して 1 のカウントを返す必要があります。 「はい」の場合は、以下のステップ 5 に進みます。 存在しない場合は、重複するキー、まだ一意の行があり、保存する行を決定する必要があります。 通常は、行を破棄するか、この行に対して新しい一意キー値を作成する必要があります。 holddups テーブル内でこのように重複する PK ごとに、次の 2 つの手順のいずれかを実行します。
  5. 元のテーブルから重複する行を削除します。 例:
    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
  6. 一意の行を元のテーブルに戻します。 例:
    INSERT t1 SELECT * FROM holddups