Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

TomBarrand

Programmer
Aug 9, 2000
162
GB
Hi,

I am trying to update a column with a view using the SQL statement below, but get the following error.

UPDATE TEST.VIEW_USR SET STRKNOWNAS = 'joe.bloggs@asasa.com WHERE PK_LNGPERSONIDCNT = 12993;

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

Any advise would be much appreciated.

Thanks
 
Make sure you have a closing quote
Code:
'joe.bloggs@asasa.com[COLOR=red]'[/color]
Do you still get the same error?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus
 
I presume VIEW_USR is a view, not a table. In this case, it is probably a join of two tables. In order to be able to update one of the tables in that situation, both tables involved in the join must have primary keys.
 
VIEW_USR is a view. The VIEW_USR view is used by a third party integration piece of software that imposes the restriction that only one record can exist in the view for one user.

The way the data is stored there is a main user table with one record per user and there is another table that stores contact methods (Phone, Fax, Email) so there will be multiple records in this table. If a join the two tables that would creat multiple records in the view for the same user.

To get round this I have created a view based on the contact methods, e.g. VIEW_EMAIL, VIEW_PHONE, VIEW_FAX then links these to the main user table based on the user id to form VIEW_USR. The SQL for the VIEW_USR is below.


SELECT TEST.PERSON.LNGPERSONIDCNT,
TEST.PERSON.BISACTIVE, TEST.PERSON.BISAPACSEMPLOYEE,
TEST.PERSON.BISLIVELINK, TEST.PERSON.LLORIGINALPASSWORD,
TEST.PERSON.LLUID, TEST.PERSON.LLUSERNAME,
TEST.PERSON.STRFIRSTNAME, TEST.PERSON.STRJOBTITLE,
TEST.PERSON.STRKNOWNAS, TEST.PERSON.STRPREFIX,
TEST.PERSON.STRSURNAME, TEST.COMPANY.STRADDRESS1,
TEST.COMPANY.STRADDRESS2, TEST.COMPANY.STRADDRESS3,
TEST.COMPANY.STRADDRESS4, TEST.COMPANY.STRADDRESS5,
TEST.COMPANY.STRCOMPANYNAME, TEST.COMPANY.STRLLABREV,
TEST.VIEW_TELEPHONE.TELEPHONE, TEST.VIEW_FAX.FAX,
TEST.VIEW_EMAIL.EMAIL
FROM TEST.COMPANY JOIN TEST.PERSON
ON TEST.COMPANY.LNGCOMPANYIDCNT = TEST.PERSON.LNGCOMPANYIDCNT
AND TEST.COMPANY.LNGSITEIDCNT = TEST.PERSON.LNGSITEIDCNT
LEFT OUTER JOIN TEST.VIEW_TELEPHONE ON TEST.PERSON.LNGPERSONIDCNT = TEST.VIEW_TELEPHONE.LNGPERSONIDCNT
LEFT OUTER JOIN TEST.VIEW_FAX ON TEST.PERSON.LNGPERSONIDCNT = TEST.VIEW_FAX.LNGPERSONIDCNT
LEFT OUTER JOIN TEST.VIEW_EMAIL ON TEST.PERSON.LNGPERSONIDCNT = TEST.VIEW_EMAIL.LNGPERSONIDCNT
 
Tom I'll admit I'm new to Oracle, I had the very same problem for a view which could update fine in SQL Server, but It could not update in Oracle.What I did to implement the update was to make both tables each have a primary key!

 
Yes, you will either need all tables involved in the view to have primary keys or do the update on the table directly.
 
I can't alter the database as it is a third party product so I might just have to update the table directly.

I'm glad thats what you said, because thats what I was thinking.

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top