UPDATE on seemingly key preserving view in Oracle raises ORA-01779

Is there any way to rewrite this query to make Oracle trust my judgement?

I've managed to "convince" Oracle to do MERGE by introducing helper column in target:

MERGE INTO (SELECT (SELECT t.account_no FROM dual) AS account_no_temp,                    t.account_no, t.contract_id             FROM t) tUSING (  SELECT u.account_no_old, u.account_no_new, v.contract_id  FROM u, v  WHERE v.tenant_id = u.tenant_id) sON (t.account_no_temp = s.account_no_old AND t.contract_id = s.contract_id)WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;

db<>fiddle demo

EDIT

A variation of idea above - subquery moved directly to ON part:

MERGE INTO (SELECT t.account_no, t.contract_id FROM t) tUSING (      SELECT u.account_no_old, u.account_no_new, v.contract_id      FROM u, v      WHERE v.tenant_id = u.tenant_id    ) sON ((SELECT t.account_no FROM dual) = s.account_no_old     AND t.contract_id = s.contract_id)WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;

db<>fiddle demo2

Related article: Columns referenced in the ON Clause cannot be updated

EDIT 2:

MERGE INTO (SELECT t.account_no, t.contract_id FROM t) tUSING (SELECT u.account_no_old, u.account_no_new, v.contract_id       FROM u, v       WHERE v.tenant_id = u.tenant_id) sON((t.account_no,t.contract_id,'x')=((s.account_no_old,s.contract_id,'x')) OR 1=2) WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;

db<>fiddle demo3

You may define a temporary table containing the pre-joined data from U and V.

Back it with a unique index on contract_id, account_no_old (which should be unique).

Then you may use this temporary table in an updateable join view.

create table tmp as  SELECT v.contract_id, u.account_no_old, u.account_no_new  FROM u, v  WHERE  v.tenant_id = u.tenant_id;create unique index tmp_ux1 on tmp ( contract_id, account_no_old);UPDATE (  SELECT t.account_no, tmp.account_no_new  FROM t, tmp  WHERE t.account_no = tmp.account_no_old  AND t.contract_id = tmp.contract_id)SET account_no = account_no_new;

Trying to do this with a simpler update. Still requires a subselect.

update tset t.account_no = (SELECT u.account_no_new  FROM u, v  WHERE t.account_no = u.account_no_old  AND t.contract_id = v.contract_id  AND v.tenant_id = u.tenant_id);

Bobby