郵便番号DBの分割されたレコードをマージする
KEN_ALLテーブルは第1正規形ではなく，本来1つであるべきレコードが複数レコードに分割されていると指摘されています．
http://www.f3.dion.ne.jp/~element/msaccess/AcTipsKenAllCsv.html
上記サイトを参考に，『Flag13（一つの郵便番号で二以上の町域を表す場合の表示）が'0'（つまり一つの郵便番号で一つの町域を表す）で、尚かつ同一郵便番号のレコードが 2 レコード以上の場合、マージする必要がある』該当レコードを抽出し，1レコードにマージします．
上記クエリの中でコアになる処理はCTE1とCTE3です．CTE1では再帰クエリを用いてIDの昇順にAREAフィールドとAREA_kanaフィールドを繋いでいきます．CTE3ではDATALEVELフィールドが最大値であるレコードを取り出しています．DATALEVELフィールドが最大値未満のレコードにはデータの欠損があります．この方法はSQL Serverフォーラムで教えていただいたものです．
WITH CTE (MINID, ZIPCODE) AS
(
SELECT MIN(ID), ZIPCode
FROM dbo.KEN_ALL
WHERE Flag13 = '0'
GROUP BY ZIPCode
HAVING COUNT(ID) > 1
),
CTE1 (ID, ZIPCODE, AREA, AREA_kana, DATALEVEL) AS
(
SELECT T1.ID, T1.ZIPCode, T1.AREA, T1.AREA_kana, 1
FROM CTE INNER JOIN dbo.KEN_ALL AS T1
ON CTE.ZIPCODE = T1.ZIPCode
WHERE T1.Flag13 = '0'
UNION ALL
SELECT T2.ID, T2.ZIPCode, CTE1.AREA + T2.AREA, CTE1.AREA_kana + T2.AREA_kana, CTE1.DATALEVEL + 1
FROM CTE1 INNER JOIN dbo.KEN_ALL AS T2
ON CTE1.ZIPCODE = T2.ZIPCode
AND CTE1.ID < T2.ID
WHERE T2.Flag13 = '0'
),
CTE2 (ZIPCODE, MAXLEVEL) AS
(
SELECT ZIPCODE, MAX(DATALEVEL)
FROM CTE1
GROUP BY ZIPCODE
),
CTE3 (ID, ZIPCODE, AREA, AREA_kana, DATALEVEL) AS
(
SELECT CTE1.ID, CTE1.ZIPCODE, CTE1.AREA, CTE1.AREA_kana, CTE1.DATALEVEL
FROM CTE2 INNER JOIN CTE1
ON CTE2.ZIPCODE = CTE1.ZIPCODE
AND CTE2.MAXLEVEL = CTE1.DATALEVEL
)
UPDATE K
SET AREA = CTE3.AREA, AREA_kana = CTE3.AREA_kana
FROM dbo.KEN_ALL AS K INNER JOIN CTE3
ON K.ZIPCode = CTE3.ZIPCODE
WHERE K.Flag13 = '0';
(550 行処理されました)
重複レコードを削除します．
DELETE FROM dbo.KEN_ALL
WHERE dbo.KEN_ALL.ID > (SELECT MIN(ID)
FROM dbo.KEN_ALL AS K1
WHERE dbo.KEN_ALL.ZIPCODE = K1.ZIPCODE
AND dbo.KEN_ALL.AREA = K1.AREA);
(466 行処理されました)
最後に確認します．
SELECT ZIPCODE, COUNT(ID)
FROM dbo.KEN_ALL
WHERE Flag13 = '0'
GROUP BY ZIPCODE
HAVING COUNT(ID) > 1;
(0 行処理されました)
