create table 仕入(
商品番号 char(1),
仕入日 date,
販売金額 number(2),
primary key(商品番号, 仕入日));
create table 販売(
商品番号 char(1),
販売日 date,
金額 number(2),
primary key(商品番号,販売日));
insert all
into 仕入 values(1,to_date('2007-07-01','yyyy-mm-dd'), 0)
into 仕入 values(1,to_date('2007-08-01','yyyy-mm-dd'), 0)
into 仕入 values(1,to_date('2007-09-01','yyyy-mm-dd'), 0)
into 仕入 values(2,to_date('2007-07-01','yyyy-mm-dd'), 0)
into 仕入 values(2,to_date('2007-08-01','yyyy-mm-dd'), 0)
into 仕入 values(3,to_date('2007-01-01','yyyy-mm-dd'), 0)
into 仕入 values(3,to_date('2007-02-01','yyyy-mm-dd'), 0)
into 販売 values(1,to_date('2007-08-10','yyyy-mm-dd'),15)
into 販売 values(1,to_date('2007-09-10','yyyy-mm-dd'),30)
into 販売 values(2,to_date('2007-07-15','yyyy-mm-dd'),20)
into 販売 values(3,to_date('2007-03-01','yyyy-mm-dd'),50)
select 1 from dual;
commit;
--■■■/*+ BYPASS_UJVC */を使う方法■■■
update (select /*+ BYPASS_UJVC */
a.販売金額,b.金額
from 仕入 a,販売 b
where a.商品番号 = b.商品番号
and a.仕入日 = (select max(c.仕入日)
from 仕入 c
where c.商品番号 = a.商品番号
and c.仕入日 < b.販売日))
set 販売金額 = 金額;
--■■■having句にこだわった方法■■■
update 仕入 a
set 販売金額 = (select c.金額
from 仕入 b,販売 c
where a.商品番号 = all(b.商品番号,c.商品番号)
and b.仕入日 < c.販売日
group by c.商品番号,c.販売日,c.金額
having a.仕入日 = max(b.仕入日))
where (商品番号,仕入日) in (select c.商品番号,max(b.仕入日)
from 仕入 b,販売 c
where b.商品番号 = c.商品番号
and b.仕入日 < c.販売日
group by c.商品番号,c.販売日);
--■■■さらにhaving句にこだわった方法■■■
update 仕入 a
set 販売金額 = (select c.金額
from 仕入 b,販売 c
where a.商品番号 = all(b.商品番号,c.商品番号)
and b.仕入日 < c.販売日
group by c.商品番号,c.販売日,c.金額
having a.仕入日 = max(b.仕入日))
where exists (select 1
from 仕入 b,販売 c
where a.商品番号 = all(b.商品番号,c.商品番号)
and b.仕入日 < c.販売日
group by c.商品番号,c.販売日,c.金額
having a.仕入日 = max(b.仕入日));
--■■■さらに変形して、group by句にRowIDを使った方法■■■
update 仕入 a
set 販売金額 = (select c.金額
from 仕入 b,販売 c
where a.商品番号 = all(b.商品番号,c.商品番号)
and b.仕入日 < c.販売日
group by c.RowID,c.金額
having a.仕入日 = max(b.仕入日))
where exists (select 1
from 仕入 b,販売 c
where a.商品番号 = all(b.商品番号,c.商品番号)
and b.仕入日 < c.販売日
group by c.RowID
having a.仕入日 = max(b.仕入日));