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

[TomBarrand]

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

 

[BJCooperIT]

Make sure you have a closing quote

'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

 

[Dagon]

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.

 

[TomBarrand]

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

 

[masiwan]

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!

 

[Dagon]

Yes, you will either need all tables involved in the view to have primary keys or do the update on the table directly.

 

[TomBarrand]

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