10-107 UpdatableViewで更新

SQLパズル

Table1        Table2
Col1  Col2    Col1  Col2
----  ----    ----  ----
   1  歩         1  と金
   2  飛車       2  龍王
   3  王将

Table2.Col2を、
Table2.Col1と等しいTable1.Col1を持つTable1.Col2で
updateする。

Table1、Table2のプライマリキーは、Col1とする。

更新結果
Table1
Col1  Col2
----  ----
   1  と金
   2  龍王
   3  王将

データ作成スクリプト

create table Table1(
Col1 number(1),
Col2 varchar2(4),
primary key(Col1));

insert into Table1 values(1,'歩');
insert into Table1 values(2,'飛車');
insert into Table1 values(3,'王将');

create table Table2(
Col1 number(1),
Col2 varchar2(4),
primary key(Col1));

insert into Table2 values(1,'と金');
insert into Table2 values(2,'龍王');
commit;

SQL

--■■■UpdatableViewを使わない方法■■■
update Table1 a
set Col2 = (select b.Col2 from Table2 b
             where b.Col1 = a.Col1)
where exists(select 1 from Table2 b
              where b.Col1 = a.Col1);

--■■■UpdatableViewを使う方法■■■
update (select a.Col2 as OldCol2,
               b.Col2 as NewCol2
          from Table1 a,Table2 b
         where a.Col1 = b.Col1)
set OldCol2 = NewCol2;

--■■■UpdatableViewを使う方法(usingを使用)■■■
update (select a.Col2 as OldCol2,
               b.Col2 as NewCol2
          from Table1 a Join Table2 b
         using (Col1))
set OldCol2 = NewCol2;

--■■■mergeを使う方法(9iなら無意味なinsert文が必要)■■■
merge into Table1 aa
using (select a.RowID as Row_ID,b.Col2 as NewCol2
         from Table1 a Join Table2 b
        using (Col1)) bb
   on (aa.RowID=bb.Row_ID)
when matched then
update set aa.Col2= bb.NewCol2;

解説

UpdatableViewでupdateしたほうが、
パフォーマンスがいいことがあります。

UpdatableViewを使わない方法は、相関サブクエリを使っていて
UpdatableViewを使う方法は、非相関サブクエリを使ってます。

こちらによると
>一般的に言って UpdatableView は以下の場合に有効で効率的。
>別表を参照する更新において旧来の方法を使うと
>set の後の副問合せと更新条件の exists での副問合せが同じ場合
>副問合せ側が2回のスキャンではなく1回のスキャンで済むから。

だそうです。
内部結合で、値の取得と、existsの存在チェックを
同時に行ってるからと考えると分かりやすいと思います。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ちなみに、UpdatableViewを使う方法では、

Table1がプライマリキーあり、Table2がプライマリキーなし
だとORA-01779: キー保存されていない表にマップする列は変更できません。

Table1がプライマリキーなし、Table2がプライマリキーあり
だとエラーになりません。

update後の値が、プライマリキーまたはユニークキーによって
一意になることが保証されないといけないのだと思いますね。

更新される側:更新に使う値を持つ側
が
多:1
か
1:1
でなければならないと言えるでしょう。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
UpdatableViewを使う方法(usingを使用)を見れば分かりますが
典型的なUpdatableViewは、
usingを使うのが向いている内部結合の一つです。
キーの数が増えてもシンプルなままなのです。

2-1-3 select文の結果を使ってupdate
10-260 3テーブルでのUpdatableView

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
UpdatableViewは使えないが、実際は1対1で結合できる場合には、
merge文をupdate文の別構文として使用するのもいいでしょう。
(BYPASS_UJVCヒントはOracle11gのマニュアルにすらのってませんし)

US-OTNでよく見るmergeの使い方
merge vs update statement

ちなみに
9iでは、下記のように無意味なnot matched句とinsert文がないと文法エラーになります。

merge into Table1 aa
using (select a.RowID as Row_ID,b.Col2 as NewCol2
         from Table1 a Join Table2 b
        using (Col1)) bb
   on (aa.RowID=bb.Row_ID)
when matched then
update set aa.Col2= bb.NewCol2
when not matched then
insert(aa.COL1,aa.COL2) values(null,null);

---