【Oracle】複数テーブルを結合したインラインビューを対象にUPDATEするときのやり方と注意点

IT

異なるＫＥＹのテーブル同士を結合しながら更新する場合のメモとか注意点

 

 
例えば以下のような２つのテーブルがあったとする
■TEST_TAB1

create table TEST_TAB1(  
    TEST_NO char(10) PRIMARY KEY  
   ,TEST_STR varchar2(100)  
)  
/


■TEST_TAB4

create table TEST_TAB4  
(  
 test_no char(10)   
,test_seq number(10)  
,test_work varchar2(100)   
,primary key(test_no , test_seq)  
)  
/



それぞれ以下のデータが入ってるとする。（★はＫＥＹ）
■TEST_TAB1

★TEST_NOTEST_STR

1 テストです。１
2 テストです。２
3 テストです。３


■TEST_TAB4

★TEST_NO★TEST_SEQTEST_WORK

1 1 テストだよーん
1 2 テストだよーん２
2 1 別レコードだよーん



「TEST_TAB1」は「TEST_NO」だけで一意になるが、
「TEST_TAB4」は「TEST_NO」「TEST_SEQ」の２つで一意になる。
実際、TEST_NO = '1'のレコードは、「TEST_TAB1」は１レコードだが、「TEST_TAB4」は２レコードになる（ように無理やりデータを用意した）。

このとき、「TEST_TAB1」と「TEST_TAB4」を結合しながら更新する場合、

-- 例１（※失敗します）  
update (select x.TEST_NO  
              ,y.TEST_SEQ  
              ,x.TEST_STR  
              ,y.TEST_WORK  
              ,x.TEST_STR || '、' || TEST_WORK as TEST_UPDATE  
        from TEST_TAB1 x   
            ,TEST_TAB4 y  
        where x.TEST_NO = y.TEST_NO  
       )  
set TEST_STR = TEST_UPDATE  
;


というようなUPDATE文は実行できない（ＯＲＡＣＬＥエラーになる）。
⇒ちなみに「ORA-01779: キー保存されていない表にマップする列は変更できません」ってのが出る

しかし一方で、

-- 例２  
update (select x.TEST_NO  
              ,y.TEST_SEQ  
              ,x.TEST_STR  
              ,y.TEST_WORK  
              ,x.TEST_STR || '、' || TEST_WORK as TEST_UPDATE  
        from TEST_TAB1 x   
            ,TEST_TAB4 y  
        where x.TEST_NO = y.TEST_NO  
       )  
set TEST_WORK = TEST_UPDATE  
;


というUPDATE文は実行できる。

要するに、更新しようとしている項目を持っているテーブルのレコードを、
更新対象のインラインビュー内で特定できない（一意になっていない）と更新できないということなのだろう。
これはテーブルの定義だけに依存しており、格納されているデータの実態とは無関係に実行可否が決定される。
⇒例えばこの例で言うと、下記のように、TEST_TAB4が実態として「TEST_NO」だけで一意になる場合でも、例１のＳＱＬは実行できない

★TEST_NO★TEST_SEQTEST_WORK

1 1 テストだよーん
2 1 別レコードだよーん


あまり深く考えずにものを言わせてもらうと、この例だと、
インラインビューでは確かにTEST_NO = '1'が２レコード返って来るわけだが、
ＫＥＹの考え方がTEST_TAB4に引っ張られてるってだけで
TEST_TAB1的にはそれで「レコードが特定できない」ってわけではない（TEST_NO = '1'のレコードを２回見ることになるだけ）
だから別に例１も実行できていいんじゃねえの？という感覚がしてしまう。

もしかしたら、
「同一ＫＥＹに対して更新かけようとして延々とロック開放待ちが発生するから」
とかいう理由で意図的に制限してるのかな？
つまりこういう場合別トランザクションになるとか？？
sqlplusとかで同一テーブル同一ＫＥＹ相手にUPDATEかけると２回目優先されて更新できるもんだけどな。
仮にそういう事情だとしても、そういうのってＯＲＡＣＬＥ的にうまくサバいてくれりゃいいのにって感じもしなくもないけどね。

まあ個人的にインラインビューをUPDATE対象に据えてる時点でなんとなく「アブねえ実装」の印象があるので
素直に一つずつテーブル更新していきゃいいんだよね要するに。