| update statement with 3-way table join [message #8862] |
Tue, 30 September 2003 12:03  |
Alexander
Messages: 109
Registered: May 2000
|
Senior Member |
|
|
Hi... This should be a very easy question.
I need help with the update statement.
I've read a lot of messages and hope that I have formulated a good update statement.
However, it doesn't work, so something is wrong.
Here's the code.
************************************************
create table shop (shopid int not null, shopname varchar2(20) null);
insert into shop (shopid, shopname) values (910,'pennys');
insert into shop (shopid, shopname) values (920,'kroger');
create table person (id int not null, lastname varchar2(20) null);
insert into person (id, lastname) values (1,'bob');
insert into person (id, lastname) values (2,'charley');
create table shopper (id int not null, lastname varchar2(20), shopid int null);
insert into shopper (id, lastname, shopid) values (1,'',910);
insert into shopper (id, lastname, shopid) values (2,'',920);
commit;
update (select b.LastName bLastName, c.LastName cLastName
from
shop a,
person b,
shopper c
where b.id = c.id
and a.shopid = c.shopid)
set cLastName = bLastName;
*********************************************
error:
set cLastName = bLastName
*
ERROR at line 8:
ORA-01779: cannot modify a column which maps to a non key-preserved table
|
|
|
|
| Re: update statement with 3-way table join [message #8863 is a reply to message #8862] |
Tue, 30 September 2003 12:47  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
When you update a join, all of the tables other than the one being updated must be "key preserved" - they must return at most one row. So, you would need to add either a PK or a unique constraint to PERSON and SHOP:
alter table person add primary key (id);
alter table shop add primary key (shopid); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
