4-19 リニアサーチで更新対象を求める

SQLパズル

T1
ID  Val
--  ---
 1    0
 2    1
 3    1
 4    4  ←更新対象
 5    3  ←更新対象
 6    2
 7    3
 8    2
 9    1

ID=6 の行から、ID=6の行のVal未満のValを持つ行を見つけ、
これらの2つの行に挟まれた行のValをインクリメントして値に更新する。

更新結果
ID  Val
--  ---
 1    0
 2    1
 3    1
 4    5
 5    4
 6    2
 7    3
 8    2
 9    1

こちらを参考にさせていただきました

データ作成スクリプト

create table T1(ID,Val) as
select 1,0 from dual union
select 2,1 from dual union
select 3,1 from dual union
select 4,4 from dual union
select 5,3 from dual union
select 6,2 from dual union
select 7,3 from dual union
select 8,2 from dual union
select 9,1 from dual;

SQL

update t1 a
set Val = Val+1
where (select max(b.id)
         from t1 b
        where b.id < 6
          and b.val <= 2) < id
  and id < 6;

解説

番兵を配置するのは、リニアサーチのアルゴリズムでの定番ですが、
番兵がいるとサブクエリがnullを返す心配がなくなるので、楽ですね。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
番兵法と似た考え方で
Xで区切りの文字列 123X456X789X12X34X56
から文字列12が存在しないか検索する際の、両端にセパレータを付与してから検索する
instr('X' || '123X456X789X12X34X56' || 'X' , 'X12X')
というのもあります。