4-22 UpdatableViewをmergeで代用(3テーブル版)

SQLパズル

Teamテーブル
Tid  Ttype  Tname
---  -----  -----
101      1  name1
102      2  name2
103      1  name3
104      3  name4
105      1  name5

Relationテーブル
Tid  Cid
---  -----
101  10003
102  10004
103  10002
104  10001
105  10005

Characterテーブル
Cid    Ccategory  Cname
-----  ---------  -------
10001  pa         hawk
10002  pa         buffalo
10003  pa         lion
10004  pa         eagle
10002  ce         swallow
10003  ce         giant
10005  ce         tiger

TeamテーブルのTtype=1のTname列を、
Tid列で紐づいたRelationテーブルのCid列で
更に紐づいたCharacterテーブルのCcategory列='ce'のレコードのCnameで更新する。

更新結果
Teamテーブル
Tid  Ttype  Tname
---  -----  -------
101      1  giant
102      2  name2
103      1  swallow
104      3  name4
105      1  tiger

データ作成スクリプト

create table Team(Tid,Ttype,Tname) as
select 101,1,'name1      ' from dual union all
select 102,2,'name2      ' from dual union all
select 103,1,'name3      ' from dual union all
select 104,3,'name4      ' from dual union all
select 105,1,'name5      ' from dual ;
alter table Team add primary key(Tid);

create table Relation(Tid,Cid) as
select 101,10003 from dual union all
select 102,10004 from dual union all
select 103,10002 from dual union all
select 104,10001 from dual union all
select 105,10005 from dual;
alter table Relation add primary key(Tid);

create table Character(Cid,Ccategory,Cname) as
select 10001,'pa','hawk'    from dual union all
select 10002,'pa','buffalo' from dual union all
select 10003,'pa','lion'    from dual union all
select 10004,'pa','eagle'   from dual union all
select 10002,'ce','swallow' from dual union all
select 10003,'ce','giant'   from dual union all
select 10005,'ce','tiger'   from dual;
alter table Character add primary key(Cid,Ccategory);

SQL

--■■■BYPASS_UJVCを使う方法■■■
update (select /*+ BYPASS_UJVC */
        a.Tname as oldVal,b.Cname as newVal
          from Team a,Character b,Relation c
         where a.Ttype=1
           and a.Tid = c.Tid
           and c.cid = b.cid
           and b.Ccategory='ce')
set oldVal = newVal;

--■■■mergeを使う方法(9iなら無意味なinsert文が必要)■■■
merge into Team d
using (select a.RowID as Row_ID,b.Cname as newVal
         from Team a,Character b,Relation c
        where a.Ttype=1
          and a.Tid = c.Tid
          and c.cid = b.cid
          and b.Ccategory='ce') e
on (d.RowID = e.Row_ID)
when matched then
update set d.Tname = e.newVal;

--■■■UpdatableViewを使わない方法■■■
update Team a
set Tname = (select c.Cname
               from Relation b,Character c
              where b.Tid = a.Tid
                and b.Cid = c.Cid
                and c.Ccategory = 'ce')
 where Ttype=1
   and exists(select 1
               from Relation b,Character c
              where b.Tid = a.Tid
                and b.Cid = c.Cid
                and c.Ccategory = 'ce');

解説

・BYPASS_UJVCはOracle11gですらマニュアルにのってない
・UpdatableViewを使わない方法は、同じ条件の2度書きが必要

なので、merge文がシンプルだと思います。
(UpdatableViewと似た記述もできますし)

10-107 UpdatableViewで更新

---