ORA-01779: 无法修改映射到非键保留表的列

2021-12-24 00:00:00 sql-update sql oracle plsql

我有这个程序:

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

但是如果我取出更新并写:

but if I take out the update and I write:

  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');

那么这很好用.你能告诉我发生了什么吗?

then this works well. Can you tell me what is happening?

推荐答案

DML 表表达式子句仅在您需要来自多个表的列时才有用.在您的情况下，您可以使用带有 EXISTS 的定期更新:

A DML table expression clause is only useful when you need columns from more than one table. In your case, you can use a regular update with an 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
    );

如果您确实需要使用两个表中的列，您有以下三种选择:

If you really do need to use columns from both tables you have three options:

  1. 重复SET 和WHERE 子句中的连接.这很容易构建，但不是最佳的.
  2. DML 表表达式.如果您有正确的索引，这 应该会起作用.

  3. MERGE，下面是一个例子.

  1. repeat the join in the SET and the WHERE clause. This is easy to build but not optimal.
  2. DML table expression. This should work, if you have the correct indexes.

  3. MERGE, below is an example.

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');

这不会直接回答您的问题，而是提供了一些变通方法.我无法重现您遇到的错误.我需要一个完整的测试用例来进一步研究它.

This does not directly answer your question, but instead provides some work-arounds. I can't reproduce the error you're getting. I'd need a full test case to look into it further.

可更新视图的主要问题之一是对它们可以包含的查询的大量限制.查询或视图不得包含大量特征，例如 DISTINCT、GROUP BY、某些表达式等.具有这些特征的查询可能会引发异常ORA-01732:此视图上的数据操作操作不合法".

One of the main problems with updatable views is the large number of restrictions on the queries they can contain. The query or view must not contain a lot of features, such as DISTINCT, GROUP BY, certain expressions, etc. Queries with those features may raise the exception "ORA-01732: data manipulation operation not legal on this view".

可更新视图查询必须明确返回修改表的每一行一次.查询必须是键保留"，这意味着 Oracle 必须能够使用主键或唯一约束来确保每一行只修改一次.

The updatable view query must unambiguously return each row of the modified table only one time. The query must be "key preserved", which means Oracle must be able to use a primary key or unique constraint to ensure that each row is only modified once.

为了说明保留密钥的重要性，下面的代码创建了一个不明确的更新语句.它创建了两个表，第一个表有一行，第二个表有两行.这些表通过 A 列连接，并尝试更新第一个表中的 B 列.在这种情况下，最好是 Oracle 阻止更新，否则该值将是不确定的.有时该值会设置为1"，有时会设置为2".

To demonstrate why key preserved is important, the below code creates an ambiguous update statement. It creates two tables, the first table has one row and the second table has two rows. The tables join by the column A, and try to update the column B in the first table. In this case it's good that Oracle prevents the update, otherwise the value would be non-deterministic. Sometimes the value would be set to "1", sometimes it would be set to "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 语句似乎试图在运行时而不是编译时检测歧义.

The MERGE statement does not have the same restrictions. The MERGE statement appears to try to detect ambiguity at run time, instead of compile time.

不幸的是，MERGE 并不总是能很好地检测歧义.在 Oracle 12.2 上，以下语句偶尔会起作用，然后会失败.对查询进行小的更改可能会使其工作或失败，但我找不到特定的模式.

Unfortunately MERGE doesn't always do a good job of detecting ambiguity. On Oracle 12.2, the below statement will occasionally work, and then fail. Making small changes to the query may make it work or fail, but I can't find a specific pattern.

--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 在编译时失败，如果理论上可能有重复.一些应该工作的语句将无法运行.

UPDATE fails at compile time if it is theoretically possible to have duplicates. Some statements that should work won't run.

MERGE 将失败.一些不应该工作的语句仍然会运行.

MERGE fails if the database detects unstable rows at run time. Some statements that shouldn't work will still run.

相关文章