INF: テーブルから重複行を削除する方法

文書翻訳 文書翻訳
文書番号: 139444 - 対象製品
この記事は、以前は次の ID で公開されていました: JP139444
すべて展開する | すべて折りたたむ

概要

重複行、および一意でない主キーは、Microsoft SQL Server 6.0,6.5,7.0 6.0,6.5,7.0 6.0,6.5,7.0 のテーブルに格納できません。ここでは、簡単のために主キーを単に "キー"、または "PK" と呼ぶことがありますが、それらの語は常に "主キー" を意味します。重複 PK は実体の整合性を損なうため、リレーショナル システムでは不許可にする必要があります。SQL Server には、インデックス、UNIQUE 制約、PRIMARY KEY 制約、トリガなど、実体の整合性を強制するためのさまざまなメカニズムがあります。

それにもかかわらず、例外的な状況の下では重複した主キーが発生する可能性があり、その場合は重複キーを除去する必要があります。主キーが重複する状況の 1 つは、SQL Server 外の非リレーショナル データ中に重複キーがあり、PK の一意性を強制しないでそのデータをインポートした場合です。主キーが重複するもう 1 つの状況は、各テーブルで実体の整合性を強制していないなど、データベースのデザイン エラーが原因となる場合です。

多くの場合、重複 PK が見つかるのは、一意のインデックスの作成時です。重複キーが見つかるとインデックスの作成は中止され、次のメッセージが表示されます。
Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.
ここでは、テーブル内の重複した主キーを検索して削除する方法について説明します。ただし、重複キーの再発を予防するために、重複が発生した理由を綿密に調べる必要があります。

詳細

この例では、次に示す、重複 PK 値を持つテーブルを使用します。このテーブルの主キーは、2 つの列 (col1、col2) から構成されています。2 つの行の PK が重複しているため、一意のインデックスおよび PRIMARY KEY 制約を作成することができません。テーブルに続くプロシージャで、重複した 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 値が重複している数を示します。

元に戻す全体を表示する
1 1 2


重複 PK 値のセットが少ない場合、それらの重複セットを手動で個々に削除することをお勧めします。たとえば、
set rowcount 1
delete from t1
where col1=1 and col2=1
rowcount の値は、指定されたキー値の重複数 n から 1 を引いた値になります。この例では、2 行が重複しているので rowcount は 1 に設定されます。col1 と col2 の値は、上記の GROUP BY クエリの結果から取られます。GROUP BY クエリが複数の行を返した場合は、それらの各行について "set rowcount" クエリを 1 回実行する必要があり、そのたびに、rowcount を当該の PK 値の重複数 n から 1 を引いた値に設定します。

行を削除する前に、その行全体が重複しているかどうかを確認する必要があります。まれに、PK 値は重複しているが、行全体としては重複していない場合があります。たとえば、社会保障番号を主キーとするテーブルで、2 人の異なる人 (行) の社会保障番号が同じで、属性が異なる状況が考えられます。このような状況では、重複キー発生の原因となったミスにより、有効な一意のデータが行に書き込まれていない可能性があります。原因を調査したり、そのデータを後で活かすために、削除する前にそのデータをコピーして保存してください。

テーブルに異なる重複 PK 値のセットが多数あると、それらを個々に削除するのは非効率的です。この場合は次の手順に従います。
  1. まず、前の例の GROUP BY クエリを実行し、重複 PK 値セットの数、および各セットの重複件数を調べます。
  2. 重複キー値を選択して、作業テーブルに書き込みます。次に例を示します。
    SELECT col1, col2, 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 は同じでも全体としては一意の行がある場合は別です。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
    

詳細

この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 139444 (最終更新日 2000-02-28) をもとに作成したものです。

プロパティ

文書番号: 139444 - 最終更新日: 2011年2月18日 - リビジョン: 6.0
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
キーワード:?
kbsqlmanagementtools kbusage ssrvprog kbhowto KB139444
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com