7-8 プライマリキーの入れ替え

SQLパズル

武将テーブル
番号    名前
----  ------
10    源義経
27    源頼朝
35    平清盛
46    平知盛

番号を、次に大きい番号に更新する(番号が最大のデータは、最小の番号に更新する)

更新結果
番号    名前
----  ------
10    平知盛
27    源義経
35    源頼朝
46    平清盛

データ作成スクリプト

create table 武将(
番号 number(2),
名前 char(6),
primary key(番号));

insert into 武将 values('10','源義経');
insert into 武将 values('27','源頼朝');
insert into 武将 values('35','平清盛');
insert into 武将 values('46','平知盛');
commit;

SQL

--■■■case式と相関サブクエリを使う方法■■■
update 武将 a
set 番号 = case when a.番号 =(select max(b.番号) from 武将 b)
                then (select min(b.番号) from 武将 b)
                else (select min(b.番号) from 武将 b where b.番号 > a.番号) end;

--■■■分析関数を使う方法1■■■
update 武将 a
set 番号 =
(select b.更新番号
   from (select
         c.番号,
         Lead(c.番号,1,(select min(cc.番号) from 武将 cc)) over(order by c.番号) as 更新番号
         from 武将 c) b
 where b.番号=a.番号);

--■■■分析関数を使う方法2■■■
update 武将 a
set 番号 =
(select b.更新番号
   from (select
         c.番号,
         nvl(Lead(c.番号,1) over(order by c.番号),min(c.番号) over(order by c.番号)) as 更新番号
         from 武将 c) b
 where b.番号=a.番号);

解説

自己結合と相関サブクエリは、
分析関数で代用できることがあります。

---