case式を使って条件分岐するupdate文を試す

t-sql

CASE式のススメ（後編）を読んで、update文でcase式を使って条件分岐するテクニックを知ってかなり感動しました。行ごとに異なる更新を1つのupdate文でできるのすごいですね。

今回はこの記事に書いてある、主キーを入れ替えるテクニックを少し応用してみたいと思います。

jQuery UIのsortableのようなリストの項目をドラッグ&ドロップで並び替えるUIがありますよね。そのデータをテーブルで管理しているとして、並び替えてデータを更新する時のupdate文を、case式を使って書いてみたいと思います。

テーブルはこんな感じ。Ordinalはリストの何番目の項目かを表す順序です。

create table #Sortable(
    Name nvarchar(5),
    Ordinal int,
    constraint UQ_Ordinal unique(Ordinal));

たとえば、テーブルにはこんなデータが入っていて、

Name  Ordinal
----- -----------
Item1 1
Item2 2
Item3 3
Item4 4
Item5 5

Item4をドラッグしてItem2のところにドロップすると、こんな風になるようにします。

Name Ordinal
----- -----------
Item1 1
Item4 2
Item2 3
Item3 4
Item5 5

ドロップした時に、Item4のOrdinalを2にして、Item2とItem3のOrdinalはそれぞれ1を加える訳なんですが、これを行うupdate文は次のように書けると思います。 @fromはドラッグ&ドロップで移動するItemのOrdinal、@toはドロップ先のOrdinalをパラメータとして渡します。上の例では@fromが4で@toが2を設定します。

update #Sortable
set Ordinal = case
    -- 移動するItem->移動先のOridnalを設定
    when Ordinal = @from then @to
    -- 移動元と移動先の間のItem
    when @to < @from then Ordinal + 1   -- 上方向への移動->Ordinalを+1
    else Ordinal - 1 end               -- 下方向への移動->Ordinalを-1
where
    -- 移動元と移動先の間にあるItemのみを対象
    Ordinal between (case when @to > @from then @from else @to end)
        and (case when @to > @from then @to else @from end)

うん、上手くいってるはず。

コード全体はこちら。