I'm trying to do an update join with a composite primary key on one side. I specify one joined key column and constants for the other key columns.
update (select r.road_name, r.road_length, u.road_name uname, u.road_length ulength
from road r join road_updates u on u.id = r.id and u.job_id = 'DAVID' and u.step = 2)
set road_name = uname, road_length = ulength;
The key of road_updates is id, job_id, step.
This satisfies the definition of key preserved - a key of the road is a key of the join - but I still get ORA-01779: cannot modify a column which maps to a non key-preserved table.
Any ideas how to get this to work as an updateable join? I can't do MERGE because I want to do this with a workspace manager instead-of trigger on a destination view and MERGE doesn't work with instead-of.
create table road (id number, road_name varchar2(30), road_length number(9), constraint road_pk primary key(id));
create table road_updates (id number, job_id varchar2(10), step number(9), road_name varchar2(30), road_length number(9)
, constraint road_updates_pk primary key(id, job_id, step));
insert into road values (1, 'Central', 500);
insert into road values (2, 'Northern', 300);
insert into road_updates values (1, 'DAVID', 1, 'Central Hwy', 501);
insert into road_updates values (1, 'DAVID', 2, 'Central Hwy.', 505);
insert into road_updates values (2, 'DAVID', 2, 'Northern Hwy.', 299);
insert into road_updates values (2, 'DAVID', 3, 'Northern Highway', 298);
-- join showing the key-preserved data:
select r.id, r.road_name, r.road_length, u.job_id, u.step, u.road_name, u.road_length
from road r join road_updates u on u.id = r.id and u.job_id = 'DAVID' and u.step = 2;
-- Update that fails:
update (select r.road_name, r.road_length, u.road_name uname, u.road_length ulength
from road r join road_updates u on u.id = r.id and u.job_id = 'DAVID' and u.step = 2)
set road_name = uname, road_length = ulength;
I could do
update road r
set (road_name, road_length) = (select road_name, road_length from road_updates u
where u.id = r.id and u.job_id = 'DAVID' and u.step = 2)
where exists (select * from road_updates u
where u.id = r.id and u.job_id = 'DAVID' and u.step = 2)
but this performs poorly with workspace manager (full scans road when it knows there are only a few updates ; takes 29 seconds instead of a few milliseconds on a normal table).
Thanks for your help.