rm /blog

IT系技術職のおっさんがIT技術とかライブとか日常とか雑多に語るブログです。* 本ブログに書かれている内容は個人の意見・感想であり、特定の組織に属するものではありません。/All opinions are my own.*

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

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


 

 
例えば以下のような2つのテーブルがあったとする
■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)
)
/



それぞれ以下のデータが入ってるとする。(★はKEY)
■TEST_TAB1

★TEST_NOTEST_STR

1 テストです。1
2 テストです。2
3 テストです。3


■TEST_TAB4

★TEST_NO★TEST_SEQTEST_WORK

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



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

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

-- 例1(※失敗します)
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文は実行できない(ORACLEエラーになる)。
⇒ちなみに「ORA-01779: キー保存されていない表にマップする列は変更できません」ってのが出る

しかし一方で、

-- 例2
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」だけで一意になる場合でも、例1のSQLは実行できない

★TEST_NO★TEST_SEQTEST_WORK

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


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

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

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