UPDATEで採番し直したい
数値がばらばらになっているカラムを1から採番し直したいときに使用できるクエリの紹介です。
以下のようなテーブルがあります。
今回は、HogeNoとSortNoの昇順で行番号を取得し、その行番号を使用してRecordIDを採番しなおします。
HogeNo | SortNo | RecordID |
---|---|---|
1000 | 1 | 32 |
1000 | 2 | 23 |
2000 | 1 | 12 |
2000 | 2 | 55 |
2000 | 3 | 99 |
UPDATE TEST SET RecordID = NewRecordID FROM TEST T INNER JOIN ( SELECT HogeNo ,SortNo , ROW_NUMBER() OVER(ORDER BY HogeNo ASC,SortNo ASC) AS NewRecordID FROM TEST ) TEMP ON T.HogeNo = TEMP.HogeNo AND T.SortNo = TEMP.SortNo
結果はこのようになります。
HogeNo | SortNo | RecordID |
---|---|---|
1000 | 1 | 1 |
1000 | 2 | 2 |
2000 | 1 | 3 |
2000 | 2 | 4 |
2000 | 3 | 5 |
以下の1文を変更することで、好きな順序で採番が可能です。
ROW_NUMBER() OVER(ORDER BY HogeNo DESC,SortNo DESC)
HogeNo | SortNo | RecordID |
---|---|---|
2000 | 3 | 1 |
2000 | 2 | 2 |
2000 | 1 | 3 |
1000 | 2 | 4 |
1000 | 1 | 5 |