5-51 グループの値を交換

SQLパズル

番号テーブル
NO  GroupNO
--  -------
 1        1
 2        1
 3        1
 4        2
 5        2
 6        3
 7        3

NOが2のレコードのGroupNOと、
NOが5のレコードのGroupNOを入れ替える。

更新結果
NO  GroupNO
--  -------
 1        2
 2        2
 3        2
 4        1
 5        1
 6        3
 7        3

データ作成スクリプト

create table 番号(
NO      number(1),
GroupNO number(1));

insert into 番号 values(1,1);
insert into 番号 values(2,1);
insert into 番号 values(3,1);
insert into 番号 values(4,2);
insert into 番号 values(5,2);
insert into 番号 values(6,3);
insert into 番号 values(7,3);
commit;

SQL

--■■■インラインビューを使わない方法■■■
update 番号 a
set GroupNO = case GroupNO
              when (select b.GroupNO from 番号 b
                     where b.NO = 2)
              then (select b.GroupNO from 番号 b
                     where b.NO = 5)
              else (select b.GroupNO from 番号 b
                     where b.NO = 2) end
where GroupNO in(select b.GroupNO from 番号 b
                  where b.NO in(2,5));

--■■■インラインビューを使う方法■■■
update (
select GroupNO,
case GroupNO
when (select b.GroupNO from 番号 b
       where b.NO = 2)
then (select b.GroupNO from 番号 b
       where b.NO = 5)
else (select b.GroupNO from 番号 b
       where b.NO = 2) end as WillGroupNO
  from 番号 a
 where GroupNO in(select b.GroupNO from 番号 b
                   where b.NO in(2,5)))
set GroupNO = WillGroupNO;

--■■■mergeを使う方法1(10g以降)■■■
merge into 番号 a
using (select RowID as Row_ID,GroupNO,
       max(decode(No,2,GroupNO)) over() as Val2,
       max(decode(No,5,GroupNO)) over() as Val5
         from 番号) b
   on (a.RowID = b.Row_ID)
when matched then
update set a.GroupNO = case a.GroupNO
                       when Val2 then Val5
                       when Val5 then Val2
                       else a.GroupNO end;

--■■■mergeを使う方法2(10g以降)■■■
merge into 番号 a
using (select RowID as Row_ID,GroupNO,
       max(decode(No,2,GroupNO)) over() as Val2,
       max(decode(No,5,GroupNO)) over() as Val5
         from 番号) b
   on (a.RowID = b.Row_ID and b.GroupNO in(Val2,Val5))
when matched then
update set a.GroupNO = case a.GroupNO
                       when Val2 then Val5
                       when Val5 then Val2 end;

解説

単純case式で、GroupNOの値に応じた値を取得してます。

■■■■■■■■■■■■■■■■■■■■■■■■
mergeを使う方法では、
マージのon句で指定した列はupdate不可なので
RowIDをon句で指定してます。