主キーのないテーブルから重複レコードを削除する
課題
主キーがないことが原因で同一レコードが複数登録されているテーブルから重複レコードを削除し、テーブル内のレコードが全て唯一な状態にする。
実行
実行環境は以下
- Windows 7 Home Premium 64bit
- SQL Server 2008 R2 Express
- SQL Server Management Studio 10.50.2500.0
今回用いるテストテーブルは次の通り
SELECT [name] , [price] FROM [fruitPrice]
name | price |
---|---|
りんご | 80 |
みかん | 30 |
みかん | 30 |
みかん | 30 |
りんご | 80 |
みかん | 30 |
このテーブルを次のような状態にしたい
name | price |
---|---|
りんご | 80 |
みかん | 30 |
上のような状態にするために、WITH句とROW_NUMBER関数を使って書いたDELETE文が下のコード
WITH [duplicate_record] AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY [name] , [price] ORDER BY [name] ) AS [SEQ] , [name] , [price] FROM [fruitPrice] ) DELETE FROM [duplicate_record] WHERE [SEQ] > 1;
ROW_NUMBER関数で同一レコードに連番を振り、番号が2以降のレコードを対象に削除するという方法。
OVER句内のORDER BYに[name]列を指定しているが、今回の場合は同一内容のレコードならばどれを残してもよいため、実は[name]列でなくても良い。[price]列でも良いし、[name] DESCとしても大丈夫。
評価
主キーがないことが一番の問題である。が、その場合でも何とか重複レコードを抽出・削除できる。
対象のテーブルの列が多い場合、非常に面倒。その場合、唯一レコードの集合を一時テーブルにでも逃がして、全レコードを消した後に一時テーブルから挿入し直したほうが楽かもしれない。
この方法で重複レコードを削除したら、時間をおかず主キー設定に移るのが今後のためと思う。