UPDATE文による一意制約違反エラー回避

UPDATE文を実行したら一意制約違反エラーが起きてしまうような場合にどうやって回避したかというお話。更新対象は大量にあるので個別対応という選択肢はなし。

問題

テーブル名は「X」
主キーはid1・id2

id1	id2	orderNum
1	a	1
1	b	2
2	a	1

テーブル「X」に対して、id2を以下のルールで更新しなければならないとき、そのまま更新すると一意制約違反エラーとなる。(1行目と2行目)

更新前	更新後
a	Ⅰ
b	Ⅰ
c	Ⅱ

回避方法

全体像はこんな感じ

他にも良い方法があるかもしれない。

詳細

①Xをコピー

CREATE TABLE X_COPY 
AS SELECT * FROM X;

表外指定で主キー制約を作ったからか、Oracleなのかまで調べてないけど、ウチの環境でやったら主キー制約が外れてコピーされた。
ここがミソかな

②X_COPYに対して更新をかける

UPDATE X_COPY SET id2='Ⅰ' WHERE id2='a';
UPDATE X_COPY SET id2='Ⅰ' WHERE id2='b';
UPDATE X_COPY SET id2='Ⅱ' WHERE id2='c';

ここは普通にUPDATE文で。

③重複を削除

DELETE X_COPY A
  A.orderNum > (
    SELECT
      MIN(B.orderNum)
    FROM
      X_COPY B
    WHERE
           B.id1 = A.id1
      AND  B.id2 = A.id2
  )
;

これでid1、id2が重複してるレコードのorderNumが小さい方が残る。*1

④Xのデータをクリア

TRUNCATE TABLE X;

⑤X_COPYのデータをXに移す

INSERT INTO X
SELECT * FROM X_COPY;

⑥X_COPYを削除

DROP TABLE X_COPY;

所感

id2じゃなくてorderNumの方を主キーにしとく設計の方が移行が楽だなあとは思った。
移行のみを考えれば確かにそうなんだが、本来の設計からだとこれが正解だよなあと思ったり。。この辺りは思想によるからシステム内で統一されてればいっか。

*1:いいかえると、最小値より大きいものを削除する