[解決済み] ORA-01779: キーが保存されていないテーブルにマップされる列を変更できません。

質問

このような手順があります。

create or replace procedure changePermissionsToRead(
datasource  in varchar2
) 
IS

begin

update 
(
select * from 
WEB_USERROLE ur ,
WEB_USERDATASOURCE ds 
where 
    ur.username = ds.username 
    and 
    ds.datasource = datasource
    and 
    ur.READ_ONLY <> 'Y'  
)
r set r.role = replace(r.role, 'FULL', 'READ');
end;

と表示され、以下のエラーが発生します。

 ORA-01779

が、更新を抜いて書くと。

  update 
(
select * from 
WEB_USERROLE ur ,
WEB_USERDATASOURCE ds 
where 
    ur.username = ds.username 
    and 
    ds.datasource = 'PIPPO'
    and 
    ur.READ_ONLY <> 'Y'  
)
r set r.role = replace(r.role, 'FULL', 'READ');

であれば、これはうまく動作します。何が起きているのか、教えてください。

解決方法を教えてください。

DMLテーブル式節は、複数のテーブルからカラムを必要とする場合にのみ有効です。 あなたの場合、通常の更新に EXISTS :

update web_userrole
set role = replace(role, 'FULL', 'READ')
where read_only <> 'Y'
    and exists
    (
        select 1/0
        from web_userdatasource
        where datasource = p_datasource
            and username = web_userrole.username
    );

もし、本当に両方のテーブルからカラムを使用する必要がある場合は、3つの選択肢があります。

1. での結合を繰り返す。 SET と WHERE 節があります。 これは構築しやすいが、最適ではない。
2. DMLテーブル式。 これは が必要です。 は、正しいインデックスがあれば動作します。

3. MERGE 以下はその一例です。

   merge into web_userrole
   using
   (
       select distinct username
       from web_userdatasource
       where datasource = p_datasource
   ) web_userdatasource on
   (
       web_userrole.username = web_userdatasource.username
       and web_userrole.read_only <> 'Y'
   )
   when matched then update
   set role = replace(role, 'FULL', 'READ');
   
   

これはご質問に対する直接的な回答ではありませんが、代わりにいくつかの回避策をご紹介します。 私はあなたが得ているエラーを再現することはできません。 これ以上調べるには、完全なテストケースが必要です。

更新可能なビューのための一般的なアドバイス

更新可能なビューの主な問題の1つは、含まれるクエリに多数の制限があることです。 クエリやビューは、DISTINCT、GROUP BY、特定の式など、多くの機能を含んでいてはいけません。 これらの機能を持つクエリは、例外 "ORA-01732: data manipulation operation not legal on this view" を発生させる可能性があります。

更新可能なビューのクエリは、変更されたテーブルの各行を一度だけ明確に返さなければなりません。 つまり、Oracle はプライマリキーまたは一意制約を使用して、各行が 1 回だけ変更されるようにすることが可能でなければなりません。

なぜKey preservedが重要なのかを説明するために、以下のコードで曖昧なupdate文を作成します。 2つのテーブルが作成され、最初のテーブルには1行、2番目のテーブルには2行の行があります。 これらのテーブルは、カラム A を更新しようとすると、カラム B を最初のテーブルの この場合、Oracle が更新を阻止するのは良いことですが、そうでなければ、値が非決定的になってしまうでしょう。 あるときは "1"、あるときは "2"に設定されるでしょう。

--Create table to update, with one row.
create table test1 as
select 1 a, 1 b from dual;

--Create table to join two, with two rows that match the other table's one row.
create table test2 as
select 1 a, 1 b from dual union all
select 1 a, 2 b from dual;

--Simple view that joins the two tables.
create or replace view test_view as
select test1.a, test1.b b_1, test2.b b_2
from test1
join test2 on test1.a = test2.a;

--Note how there's one value of B_1, but two values for B_2.
select *
from test_view;

A  B_1  B_2
-  ---  ---
1    1    1
1    1    2

--If we try to update the view it fails with this error:
--ORA-01779: cannot modify a column which maps to a non key-preserved table
update test_view
set b_1 = b_2;

--Using a subquery also fails with the same error.
update
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
)
set b_1 = b_2;

は MERGE 文には同じ制限はありません。 そのため MERGE 文は、コンパイル時ではなく、実行時に曖昧さを検出しようとしているように見えます。

残念ながら MERGE は、あいまいさをうまく検出できないことがあります。 Oracle 12.2において、以下の文は時折動作し、その後失敗することがあります。 クエリに少し変更を加えると、動作したり失敗したりすることがありますが、特定のパターンを見つけることができません。

--The equivalent MERGE may work and changes "2" rows, even though there's only one.
--But if you re-run, or uncomment out the "order by 2 desc" it might raise:
--  ORA-30926: unable to get a stable set of rows in the source tables
merge into test1
using
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
    --order by 2 desc
) new_rows
    on (test1.a = new_rows.a)
when matched then update set test1.b = new_rows.b_2;

UPDATE は、理論上重複する可能性がある場合、コンパイル時に失敗します。 以下のようなステートメントもあります。 が必要です。 が動作しない。

MERGE は、実行時にデータベースが不安定な行を検出した場合、失敗します。 以下のようなステートメントがあります。 はいけません。 が動作しても、まだ実行されます。