10-327 最大下界の行でupdate

SQLパズル

MasterT
ID   SK  grade  amount
---  --  -----  ------
111   1  A      123
111   4  B      456
111   7  C      789
222   6  A      234
222   8  B      567
222  11  C      890

tranT
ID   SK  grade  amount
---  --  -----  ------
111   1  X      0
111   3  X      0
111   4  X      0
111   6  X      0
111   7  X      0
111   8  X      0
111   9  X      0
222   0  X      0
222   6  X      0
222   7  X      0
222   8  X      0
222   9  X      0
222  11  X      0
222  12  X      0

tranTのgradeとamountを
同じIDでSKが最大下界なMasterTの行の値でupdateする。
なお、最大下界なMasterTの行が存在しない行は、updateしない。

更新結果
ID   SK  grade  amount
---  --  -----  ------
111   1  A      123
111   3  A      123
111   4  B      456
111   6  B      456
111   7  C      789
111   8  C      789
111   9  C      789
222   0  X        0
222   6  A      234
222   7  A      234
222   8  B      567
222   9  B      567
222  11  C      890
222  12  C      890

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

データ作成スクリプト

create table MasterT(ID,SK,grade,amount) as
select 111, 1,'A',123 from dual union all
select 111, 4,'B',456 from dual union all
select 111, 7,'C',789 from dual union all
select 222, 6,'A',234 from dual union all
select 222, 8,'B',567 from dual union all
select 222,11,'C',890 from dual;

create table tranT(ID,SK,grade,amount) as
select 111, 1,'X',0 from dual union all
select 111, 3,'X',0 from dual union all
select 111, 4,'X',0 from dual union all
select 111, 6,'X',0 from dual union all
select 111, 7,'X',0 from dual union all
select 111, 8,'X',0 from dual union all
select 111, 9,'X',0 from dual union all
select 222, 0,'X',0 from dual union all
select 222, 6,'X',0 from dual union all
select 222, 7,'X',0 from dual union all
select 222, 8,'X',0 from dual union all
select 222, 9,'X',0 from dual union all
select 222,11,'X',0 from dual union all
select 222,12,'X',0 from dual;

SQL

merge into tranT a
using (select b.RowID as Row_ID,c.GRADE,c.AMOUNT,
       Row_Number() over(partition by b.RowID order by c.SK desc) as rn
         from tranT b Join MasterT c
           on b.ID=c.ID
          and b.SK >= c.SK) d
on (a.RowID=d.Row_ID and d.rn=1)
when matched then
update set a.GRADE=d.GRADE,
           a.AMOUNT=d.AMOUNT;

解説

内部結合でusingを使えない珍しいケースですね

SQLアタマ養成講座：第5回　SQL流行間比較(1)
最大下界と最小上界

---