Ora 01779 ошибка

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. 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.

Generic advice for updatable views

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».

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.

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;

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.

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 fails at compile time if it is theoretically possible to have duplicates. Some statements that should work won’t run.

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

I’m getting «ORA-01779: cannot modify a column which maps to a non key-preserved table» when I try to update a join. I’ve searched around the site and found a lot of advice on what key-preserved means and why it is necessary… but as near as I can tell I’m complying with that advice, and still getting the error.

I have two tables:

PG_LABLOCATION has, among other things, the columns:
"LABLOCID" NUMBER,
"DNSNAME" VARCHAR2(200 BYTE)

LABLOCID is the primary key, DNSNAME has a unique constraint

PG_MACHINE has, among other things, the columns:
"MACHINEID" NUMBER, 
"LABLOCID" NUMBER, 
"IN_USE" NUMBER(1,0) DEFAULT 0, 
"UPDATE_TIME" TIMESTAMP (6) DEFAULT '01-JAN-1970'

MACHINEID is a primary key
LABLOCID is a foreign key into LABLOCID in PG_LABLOCATION (its primary key)

The update I’m running is:

update 
  (select mac.in_use, mac.update_time
     from pg_machine mac 
     inner join pg_lablocation loc
       on mac.lablocid = loc.lablocid
     where loc.dnsname = 'value'
       and '02-JAN-2013' > mac.update_time
  )
set in_use = 1 - MOD( 101, 2 ), update_time = '02-JAN-2013';

I’m only updating values in one table (PG_MACHINE) and the join column in the other table is the primary key, which should make it key-preserved by my reading. I’m concerned that the where clause is causing the problem, but I tried removing the filter on mac.update_time and got the same error, and loc.dnsname has a unique constraint.

What’s even odder is that we have, like many folks, a dev and a prod environment. We did a complete schema and data migration from prod to dev. I’ve looked them both over and they have identical indexes and constraints. The query works in dev, but generates the above error in prod.

So two questions:

1) Can you see what’s wrong with my query?
2) Can you suggest what might be different between my dev and prod environment (e.g. server settings) that could cause this error in one but not the other?