Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can't update key preserved table using composite primary key

davidp 2Jun 3 2013 — edited Jun 3 2013
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 1 2013
Added on Jun 3 2013
5 comments
487 views