5-28 update文で連番に更新

SQLパズル

NoTableテーブル
No       data
--   --------
 1   ああああ
 2   いいい
 4   ううう
 9   ５５５５

NoTableテーブルのNoを、連番に更新する。

更新結果
No       data
--   --------
 1   ああああ
 2   いいい
 3   ううう
 4   ５５５５

データ作成スクリプト

create Table NoTable(
no number(1),
data varchar2(8));

insert into NoTable(No,data) values(1,'ああああ');
insert into NoTable(No,data) values(2,'いいい');
insert into NoTable(No,data) values(4,'ううう');
insert into NoTable(No,data) values(9,'５５５５');
commit;

SQL

--■■■相関サブクエリを使う方法■■■
update NoTable a
set No = (select count(b.No)+1 from NoTable b
           where b.No < a.No);

--■■■分析関数を使う方法■■■
update NoTable a
set No= (select Rank
           from (select RowID as Row_ID,
                 Row_Number() over(order by No) as Rank
                 from NoTable) b
          where b.Row_ID=a.RowID);

--■■■update前の大小関係を保持しない方法■■■
update NoTable set No = RowNum;

解説

update前の大小関係を保持しないのであれば、RowNumが使えます。