3-43 更新可能なインラインビューでのwith句

SQLパズル

TargetTable
ID  Status
--  -----------
 1  WillUpdated
 2  WillDeleted
 4  WillMerged

with句を使ったインラインビュー経由で
・Update文
・Delete文
・Insert文
・Merge文
を実行する。

更新結果
ID  Status
--  -----------
 1  Updated
 3  Inserted
 4  Merged

データ作成スクリプト

create table TargetTable(ID,Status) as
select 1,'WillUpdated' from dual union all
select 2,'WillDeleted' from dual union all
select 4,'WillMerged'  from dual;

SQL

update (with tmp as(
        select * from TargetTable)
        select * from tmp)
set Status = 'Updated'
where ID = 1;

delete (with tmp as(
        select * from TargetTable)
        select * from tmp)
where ID = 2;

insert into TargetTable(ID,Status)
with tmp as(
select '3','Inserted' from dual)
select * from tmp;

merge into(with tmp1 as(
           select * from TargetTable)
           select * from tmp1) a
using (with tmp2(ID,Status) as(
       select '4','Merged' from dual)
       select * from tmp2) b
on (a.ID = b.ID)
when matched then
update set a.Status = b.Status;

解説

マニュアルに記述されている通り、Oracleのwith句は、select文の一部ですので、
select文が記述できる箇所では、with句を使うことができます。

ちなみに、select文のfrom句でのwith句の使用例は、下記になります。

select *
  from (with tmp(Val) as(
        select 123 from dual)
        select Val from tmp);

Val
---
123

また、select文のselect句でのスカラーサブクエリでのwith句の使用例は、下記になります。

select (with tmp(Val) as(
        select 123 from dual)
        select Val from tmp) as Test1,
       (with tmp(Val) as(
        select 456 from dual)
        select Val from tmp) as Test2
from dual;

Test1  Test2
-----  -----
  123    456


マニュアルのselect文のBNF

select::=


subquery_factoring_clause::=


subquery::=



query_block::=

---