5-8 update文で相関サブクエリ

SQLパズル

TAB_A     TAB_B
 X   Y     L   M
--  --    --  --
 1   9     1   3
 2   9

TAB_A.XとTAB_B.Lを結合し、TAB_A.Y列をTAB_B.M列の値で更新する。
TAB_A.Xに結合するTAB_B.Lの値が存在しないときは0で更新する。

(TAB_A.XとTAB_B.L列はプライマリキー)

更新結果
TAB_A
 X   Y
--  --
 1   3
 2   0

データ作成スクリプト

create table TAB_A(
X number(1),
Y number(1));

create table TAB_B(
L number(1),
M number(1));

insert into TAB_A(X,Y) values(1,9);
insert into TAB_A(X,Y) values(2,9);
insert into TAB_B(L,M) values(1,3);
commit;

SQL

--■■■nvl関数を使用■■■
update TAB_A a
set Y = nvl((select b.M from TAB_B b
              where b.L = a.X),0);

--■■■case when existsを使用■■■
update TAB_A a
set Y = case when exists(select 1   from TAB_B b where b.L = a.X)
             then       (select b.M from TAB_B b where b.L = a.X)
        else 0 end;

解説

update文でも、
テーブル名にエイリアスをつけての相関サブクエリを使用できます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
下記のUpdatableViewは、
ORA-01779(cannot modify a column which maps to a non key-preserved table)
になりました。

update (select a.Y as oldVal,nvl(b.M,0) as NewVal
          from TAB_A a,TAB_B b
         where a.X = b.L(+))
set oldVal = NewVal;