4-21 updatableViewと複数列update

SQLパズル

tbl1           tbl2
PKey  kmk      PKey  kmk1  kmk2  kmk3  kmk4  kmk5
----  ---      ----  ----  ----  ----  ----  ----
   1  XX          1  AA    CC    null  null  null
   2  YY          2  BB    null  null  null  null
   3  ZZ          3  null  null  null  null  null

tbl2のnullの列の中で最も左の列を、
PKeyで紐づいた、tbl1のkmkで更新する。

更新結果
tbl2
PKey  kmk1  kmk2  kmk3  kmk4  kmk5
----  ----  ----  ----  ----  ----
   1  AA    CC    XX    null  null
   2  BB    YY    null  null  null
   3  ZZ    null  null  null  null

データ作成スクリプト

create table tbl1(PKey primary key,kmk) as
select 1,'XX' from dual union
select 2,'YY' from dual union
select 3,'ZZ' from dual;

create table tbl2(
PKey int primary key,
kmk1 char(2),
kmk2 char(2),
kmk3 char(2),
kmk4 char(2),
kmk5 char(2));

insert into tbl2
select 1,'AA','CC',null,null,null from dual union
select 2,'BB',null,null,null,null from dual union
select 3,null,null,null,null,null from dual;
commit;

SQL

--■■■複数列updateを使う方法■■■
update tbl2 a
set (kmk1,kmk2,kmk3,kmk4,kmk5) =
(select case when a.kmk1 is null then b.kmk
             else a.kmk1 end,
        case when a.kmk2 is null and a.kmk1 is not null then b.kmk
             else a.kmk2 end,
        case when a.kmk3 is null and a.kmk2 is not null then b.kmk
             else a.kmk3 end,
        case when a.kmk4 is null and a.kmk3 is not null then b.kmk
             else a.kmk4 end,
        case when a.kmk5 is null and a.kmk4 is not null then b.kmk
             else a.kmk5 end
   from tbl1 b
  where b.PKey = a.PKey)
 where exists(select 1 from tbl1 b
               where b.PKey = a.PKey);

--■■■updatableViewを使う方法■■■
update (select case when a.kmk1 is null then b.kmk
                    else a.kmk1 end as newKmk1,
               case when a.kmk2 is null and a.kmk1 is not null then b.kmk
                    else a.kmk2 end as newKmk2,
               case when a.kmk3 is null and a.kmk2 is not null then b.kmk
                    else a.kmk3 end as newKmk3,
               case when a.kmk4 is null and a.kmk3 is not null then b.kmk
                    else a.kmk4 end as newKmk4,
               case when a.kmk5 is null and a.kmk4 is not null then b.kmk
                    else a.kmk5 end as newKmk5,
          Kmk1 as oldKmk1,Kmk2 as oldKmk2,Kmk3 as oldKmk3,Kmk4 as oldKmk4,Kmk5 as oldKmk5
   from tbl2 a,tbl1 b
  where b.PKey = a.PKey)
set oldKmk1 = newKmk1,oldKmk2 = newKmk2,oldKmk3 = newKmk3,oldKmk4 = newKmk4,oldKmk5 = newKmk5;

解説

1つ左の列がnullかで場合分けしています。

更新する列数が多い時は、
updatableViewではなく、複数列updateを使うのもいいかもしれません。

2-1-3 select文の結果を使ってupdate

---