CASE式を使用して、主キーを入れ替える方法 SQL

こんにちは山田です。

気温もぐーんとあがり、
やっと春らしくなってきました。今日この頃。

さて、SQLの話題です。既存コードをリファクターできそうなものを見つけたので、その備忘録になります。

■目的
「主キー」もしくは「ユニークキー」の値を入れ替えて、
並び替え機能をつくります。

■テーブルの前提
　1. ソートキーはユニークである
　2. ソートキーが大きいものが上（降順）で並べる

表であらわすと↓こんな感じになります。

+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 1   | ｾﾞﾝｻｲ      | 100    |
| 2   | ﾒｲﾝﾘｮｳﾘ    | 10     |
| 3   | ｽｰﾌﾟ       | 1      |
+-----+------------+--------+

このソートキー「100」と「10」を入れ替えて登録して、並び替え機能をつくります
　　　　　　　　　↓

+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 2   | ﾒｲﾝﾘｮｳﾘ    | 100    |
| 1   | ｾﾞﾝｻｲ      | 10     |
| 3   | ｽｰﾌﾟ       | 1      |
+-----+------------+--------+

      　　　　　　　　　　※IDとNameは対応しています。

■仮数字を一時的に代入する方法（元々の方法）

update mst_data
   set Sorter = '-1'
　　where where ID = '1'  /*ID「1」のSoterを一時的に「-1」を代入*/
update mst_data
   set Sorter = '100'
　　where where ID = '2'　/*ID「2」のSorterに、空いた「100」を代入*/
update mst_data
   set Sorter = '10'
　　where where ID = '1'　/*ID「1」のSorterに目的の「10」を代入*/

この3つのクエリ文でトランザクションを作ってやっていました。
動作するものの、以下の懸念点がありました。
　・クエリ文が3つできてしまう
　・仮数字がすでに使われていた場合、ユニーク制約のため、-1が代入できなくなる


この解決方法として、CASE式を使用した方法をとります。

■CASE式を使って、ユニークキーを入れ替える方法

update mst_data
 set sorter = CASE WHEN sorter = '100' THEN '10'
      　　　　　　　　　 WHEN sorter = '10'  THEN '100'
　　　　　　　 　　　    ELSE sorter END
 where sorter in( '100' , '10');

CASE式を使用することによって、一度のUPDATE文で入れ替えが完了できてしまいました。
どうしてこんなことが出来てしまうかというと、
CASE式の分岐による更新は「一気」におこなわれるので、
ユニークキーの重複エラーを回避することができるのです。


実はこの方法、主キーを入れ替えるという荒業も簡単にできてしまいます。
■CASE式を使って、主キーを入れ替える方法

操作対応表　
・SQL Server： ○
・Oracle　　： ○
・MySQL　　： ×

update mst_data
 set sorter = CASE WHEN id = '1' THEN '2'
　　　　　　     　　　  WHEN id = '2' THEN '1'
　　　　　　　  　　　   ELSE id END
 where sorter in( '1' , '2');

その場合の実行結果

+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 1   | ｾﾞﾝｻｲ      | 100    |
| 2   | ﾒｲﾝﾘｮｳﾘ    | 10     |
| 3   | ｽｰﾌﾟ       | 1      |
+-----+------------+--------+

↓

+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 2   | ｾﾞﾝｻｲ      | 100    |
| 1   | ﾒｲﾝﾘｮｳﾘ    | 10     |
| 3   | ｽｰﾌﾟ       | 1      |
+-----+------------+--------+

■参考サイト
『達人に学ぶ SQL徹底指南書』サポートページ
http://www.geocities.jp/mickindex/database/db_support_sinan.html