【SQL Server】重複データをループで削除する方法

Question

前提・実現したいこと

重複データを論理削除でUPDATEするSQL文を作成しています。

以下のようなテーブルがあるとします。

order_id	model	color_code	color_name	size_code	size_name	insert_date	del_flg
45	TD001	NULL	BL	NULL	L	2021-08-05 14:22..	0
54	TD001	NULL	BL	NULL	L	2021-08-05 14:26..	0
59	TD001	NULL	BL	NULL	L	2021-08-05 14:30..	0
80	TD001	NULL	BL	NULL	L	2021-08-05 14:40..	0
46	TD002	NULL	GN	NULL	M	2021-08-06 14:22..	0
55	TD002	NULL	GN	NULL	M	2021-08-06 14:26..	0
60	TD002	NULL	GN	NULL	M	2021-08-06 14:30..	0
81	TD002	NULL	GN	NULL	M	2021-08-06 14:40..	0

・order_id(PK,bigint,NULL以外)　一意
・model(nvahar(100)、NULL以外)
・color_code(nvahar(100)、NULL)
・color_name(nvahar(100)、NULL)
・size_code(nvahar(100)、NULL)
・size_name(nvahar(100)、NULL)
・insert_date(datetime、NULL)
・del_flg(bit、NULL以外) 0 or 1

該当のソースコード

要件
・重複データの中で、insert_dateで最も新しいデータ以外の古いデータをUPDATEする。
・UPDATEで削除対象のdel_flgを0→1にする。
・各カラムの値を変数に入れて、ループを回したい。。
┗ 上記例だと抽出後2レコードのため、1回ループで2回試行する。

現状、UPDATEはできているのですが主に
・マジックナンバーで指定している
・ループを回す必要がある
ため改修の必要があります。ご教示お願いします。

※追記
現時点でループを使うパターンでしか考えが及びませんでした
本来、ループ使わない方が望ましいので、使わないパターンでご教授いただけると大変助かります。

※追記2
運用上、最も新しいデータのみ残すのが正解でしたので要件を変更しました。
昇順から降順になります。

-- 重複データの抽出
SELECT 
  T2.model,T2.color_code,T2.color_name,T2.size_code,T2.size_name ,count(*)  
FROM 
  dbo.order1_trn AS T2
WHERE 
  T2.del_flg='0'
GROUP BY 
  T2.model,T2.color_code,T2.color_name,T2.size_code,T2.size_name 
HAVING 
  count(T2.del_flg) > 1

-- UPDATE対象を確認。(insert_dateで最も古いデータ以外がUPDATE対象)

SELECT order_id,insert_date,record_num FROM (
	SELECT 
	  order_id
	 ,insert_date
	 ,ROW_NUMBER() OVER (ORDER BY insert_date ASC) AS record_num 
	FROM 
	  dbo.order1_trn 
	WHERE 
	  del_flg='0' 
	  AND model='TD001' 
	  AND color_code IS NULL 
	  AND color_name='BL' 
	  AND size_name='L' 
	  AND size_code IS NULL 
) AS T2 
WHERE 
  record_num <=(SELECT count(*)-1 FROM dbo.order1_trn WHERE del_flg='0' AND model='TD001' AND color_code IS NULL AND color_name='BL' AND size_name='L' AND size_code IS NULL )

-- 対象をUPDATEする
UPDATE dbo.order1_trn SET del_flg='1' WHERE order_id IN(
SELECT order_id FROM (
	SELECT 
	  order_id
	 ,insert_date
	 ,ROW_NUMBER() OVER (ORDER BY insert_date ASC) AS record_num 
	FROM 
	  dbo.order1_trn 
	WHERE 
	  del_flg='0' 
	  AND model='TD001' 
	  AND color_code IS NULL 
	  AND color_name='BL' 
	  AND size_name='L' 
	  AND size_code IS NULL 
) AS T2 
WHERE 
  record_num <=(
    SELECT 
	  count(*)-1 
	FROM 
	  dbo.order1_trn 
	WHERE del_flg='0' AND model='TD001' AND color_code IS NULL AND color_name='BL' AND size_name='L' AND size_code IS NULL )
)

-- 結果確認用

SELECT 
  T2.model,T2.color_code,T2.color_name,T2.size_code,T2.size_name ,count(*)  
FROM 
  dbo.order1_trn AS T2
WHERE 
  T2.del_flg='0'
GROUP BY 
  T2.model,T2.color_code,T2.color_name,T2.size_code,T2.size_name 
HAVING 
  count(T2.del_flg) > 1

補足情報（FW/ツールのバージョンなど）

SQL Server Management Studio 15.0.18382.0

Comments

[ishina_yum]

要件が良く分からないのですが、これは何かの課題でしょうか？
『ループを使う』というのは言語は何を想定しているのでしょう？
ストアドプロシージャ（Transact-SQL）ですか？　それともC#やVB.Net？ もしくはJava？
PHP、Ruby、Pythonなどのスクリプト？

> 抽出後2レコードのため、2回ループさせたい
ちょっと意味が分からない……
ループは１回じゃないの？？
というか、ループせずにSQL一発でUPDATEしたらダメなの？

[zeze]

分かりにくくてすみません。。
> これは何かの課題でしょうか？
仕事でツールを作成しています。SQLは初心者でして勉強しています。。
>『ループを使う』というのは言語は何を想定しているのでしょう？
> ループせずにSQL一発でUPDATEしたらダメなの？
Transact-SQLを想定していますが、ループしなくて済むなら一発で実行したいです。
ループで回すことしか現時点では考えられませんでした。。
> ループは１回じゃないの？？
1回ですね。。間違えました。1回ループで2回試行します。

Answer 1

削除対象の一覧はこんな感じで出せるわけです。

SELECT *
FROM
(
SELECT 
    order_id
,   model
,   color_code
,   color_name
,   size_code
,   size_name
,   insert_date
,   ROW_NUMBER() OVER
        (PARTITION BY   -- 重複検査をしたいカラムをここで並べる
            model
        ,   color_code
        ,   color_name
        ,   size_code
        ,   size_name
        ORDER BY insert_date ASC   -- 一番古い物を残すため昇順
        ) AS record_num 
FROM 
    dbo.order1_trn
) x
WHERE x.record_num > 1

重複を探したいものをROW_NUMBER() OVERのPARTITION BYに指定してやればその中で１から順に番号が振られます。
もし、重複しているレコードがなければ、1だけですね。

そして、1を除去してやれば、重複している残りだけになります。

あとはこれをUPDATEに組み込めば終わりです。

基本的には、対象となるレコードを抽出するSELECT分を作ることをとにかく考えることです。

重複の抽出には色々と方法がありますが、たとえばGROUP BYしてinsert_date の最小値を取ってそれ以外とする場合、万が一、insert_dateまで同じレコードがあった場合に処理が漏れることになります。

そんなデータは絶対にない！という場合でも、重複の検査に使う model などにNULLがある場合を考えると面倒なことになります。

それも絶対にありえない！という確証があればそれでもいいですけれど、確認を怠ると面倒なことになります。

そういう細かい条件（実際にどのようなデータがあるか）は作るSQLにも影響しますので、可能な限り事前に確認するよう心がけましょう。

Comments

[zeze]

回答ありがとうございます。UPDATEできました！
万が一を想定して各カラムの事前確認が大事ですね。精進します！

Answer 2

重複データの中で、insert_dateで最も古いデータ1つ以外をUPDATEする。
-- UPDATE対象を確認。(insert_dateで最も古いデータ以外がUPDATE対象)

2021/08/19 22:21 のコメントに基づき、SQL文を編集

model 毎に 最新の insert_date に該当しなくなったレコード
の del_flg列 を 1 に更新したい
ということですよね。
↓

• model
• color_code
• color_name
• size_code
• size_name

毎に
最古の insert_date に該当しないレコードの del_flg 列 を 1 に更新
というロジックに変更。

色々な記述方法があるのですけど、ここでは２種類のSQLを載せておきます。

UPDATE q
   SET del_flg = 1
FROM
(
    SELECT order_id
         , model
         , del_flg
         , row_number() 
               over( partition by model
                                , coalesce( color_code, N'' )
                                , coalesce( color_name, N'' )
                                , coalesce( size_code,  N'' )
                                , coalesce( size_name,  N'' )
                         order by insert_date asc
                                , order_id    asc
               ) grank
     FROM order1_trn
    WHERE del_flg = 0
) q
WHERE grank > 1
;

UPDATE x
   SET del_flg = 1
  FROM order1_trn x
 WHERE x.del_flg = 0
   AND EXISTS (
           SELECT 1 FROM order1_trn y
            WHERE y.del_flg = 0
              AND y.model = x.model
              AND coalesce( y.color_code, N'' ) = coalesce( x.color_code, N'' )
              AND coalesce( y.color_name, N'' ) = coalesce( x.color_name, N'' )
              AND coalesce( y.size_code,  N'' ) = coalesce( x.size_code,  N'' )
              AND coalesce( y.size_name,  N'' ) = coalesce( x.size_name,  N'' )
              AND ( y.insert_date < x.insert_date
                      OR
                    y.insert_date = x.insert_date 
                      AND
                    y.order_id < x.order_id
                  )
       )
;

Comments

[zeze]

回答ありがとうございます。
ご提案いただいた内容、現時点で再現できていませんが
model 毎に 最新の insert_date に該当しなくなったレコード　ではないです。
データの例が悪かったですね。。すみません。
model,color_code,color_name,size_code,size_name の5カラムで重複を確認して
得られた中で古いレコード1つを残して、残りをUPDATEしたいです。
こちらですと、下記のようにmodel TD001が同じで、他4つが異なっている場合もデータとしては含まれています。
TD001 NULL BL NULL L 2021-08-05 14:22.. 0
TD001 NULL GN NULL L 2021-08-05 14:23.. 0
TD001 NULL PI NULL M 2021-08-05 14:24.. 0

[mayu-]

- color_code(X) と color_name(Y)
- size_code(X) と size_name(Y)
は、それぞれ関数従属(X → Y)、
つまり、(key → value)の関係にはならないのでしょうか。

そうすると、母集団( グルーピング項目 )に指定する列は
- model
- color_code
- size_code
の３つに限定できそうなのですけど、何か事情がありそうですね。

[zeze]

回答ありがとうございます。
従属関係にはなりません。

[zeze]

1つめのSQL試しましたが、無事処理できました。
ありがとうございます。coalesceやNプレフィックスなど理解して使いこなしたいと思います。

[mayu-]

入れ違いになりました。エラーの原因はフィールドの型違いだったようですね。
ご自身で処理できたようで何よりです。

テーブル定義の箇所：
・del_flg(bit、NULL以外) 0 or 1

SQL文の記述：
( 単一引用符で値を括る記述はbit型では使いません。varcharなどの文字列型 )
del_flg='0'