ORA-01779: cannot modify a column which maps to a non key-preserved table

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_userroleset 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_userroleusing(    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 updateset 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 asselect 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 asselect 1 a, 1 b from dual union allselect 1 a, 2 b from dual;--Simple view that joins the two tables.create or replace view test_view asselect test1.a, test1.b b_1, test2.b b_2from test1join 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    11    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 tableupdate test_viewset 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 tablesmerge into test1using(    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.