Sergio Exp�sito wrote : >Hi: >We have a problem with "updateable join view table". We create two tables and >a view on these tables, but we can't execute an update on the view. >CREATE TABLE p1 (id numeric, text1 char(100), text2 char(100), PRIMARY KEY (id)) >CREATE TABLE p2 (id numeric, text1 char(100), text2 char(100), id_p1 numeric, PRIMARY >KEY (id), FOREIGN KEY (id_p1) REFERENCES p1(id)) >INSERT INTO p1 (id, text1, text2) VALUES (1, 'text1', 'text1') >INSERT INTO p1 (id, text1, text2) VALUES (2, 'text2', 'text2') >INSERT INTO p1 (id, text1, text2) VALUES (3, 'text3', 'text3') >INSERT INTO p1 (id, text1, text2) VALUES (4, 'text4', 'text4') >INSERT INTO p1 (id, text1, text2) VALUES (5, 'text5', 'text5') >INSERT INTO p1 (id, text1, text2) VALUES (6, 'text6', 'text6') >INSERT INTO p1 (id, text1, text2) VALUES (7, 'text7', 'text7') >INSERT INTO p1 (id, text1, text2) VALUES (8, 'text8', 'text8') >INSERT INTO p1 (id, text1, text2) VALUES (9, 'text9', 'text9') >INSERT INTO p1 (id, text1, text2) VALUES (10, 'text10', 'text10') >INSERT INTO p2 (id, text1, text2, id_p1) VALUES (20, 'TEXT20', 'TEXT20', 1) >INSERT INTO p2 (id, text1, text2, id_p1) VALUES (21, 'TEXT21', 'TEXT21', 3) >INSERT INTO p2 (id, text1, text2, id_p1) VALUES (22, 'TEXT22', 'TEXT22', 7) >INSERT INTO p2 (id, text1, text2, id_p1) VALUES (23, 'TEXT23', 'TEXT23', 10) >INSERT INTO p2 (id, text1, text2) VALUES (24, 'TEXT24', 'TEXT24') >INSERT INTO p2 (id, text1, text2) VALUES (25, 'TEXT25', 'TEXT25') >CREATE VIEW MI_VISTA (ID_P1, TEXT1_P1, TEXT2_P1, ID_P2, TEXT1_P2, TEXT2_P2) AS >SELECT P1.ID, P1.TEXT1, P1.TEXT2, P2.ID, P2.TEXT1, P2.TEXT2 FROM P1, P2 WHERE P1.ID = >P2.ID_P1 >WITH CHECK OPTION >The update statements are: >UPDATE MI_VISTA SET TEXT1_P1='text' WHERE ID_P1=1 >UPDATE MI_VISTA SET TEXT1_P1='text' WHERE ID_P1=1 AND ID_P2=20 >The SQLStudio's error are: >---- Error ------------------------------- >Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed >General error;-5013 POS(1) Missing update column. >UPDATE MI_VISTA SET TEXT1_P1='text' WHERE ID_P1=1 >---- Error ------------------------------- >Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed >General error;-5013 POS(1) Missing update column. >UPDATE MI_VISTA SET TEXT1_P1='text' WHERE ID_P1=1 AND ID_P2=20
>Why ??? Updatable join views do not allow all kinds of update known from base tables. The reason for this is the fact, that one row of the referenced table may be shared by more than one 'row' of the view. Assume that there exists another row in your table p2 : INSERT INTO p2 (id, text1, text2, id_p1) VALUES (24, 'TEXT20', 'TEXT20', 1) which references row 1 of table p1 (like p1(20)). Now assume the following update : update MI_VISTA SET TEXT1_P1 = 'text' where ID_P2 = 20. If one would update the row of p1, the row ID_P2 = 24 would have been affected too, which is of course not acceptable. You could now argue, that you specified the foreign key in the qualification of your update. i.e. all rows referencing the p1 row are to be updated. This is true, but the kernel does not analyze the qualification (yet) and does not detect this situation. I recommend to execute this kind of update via the base table. Regards, Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
