generally no as join conditions do not guarantee 1)the column to be inserted is unique 2)the column is updatable //Create a View based on 2 tables joined on location+id CREATE VIEW locations_view AS SELECT d.department_id, d.department_name, l.location_id, l.city FROM departments d, locations l WHERE d.location_id = l.location_id;
//find out which columns are updateable from the view SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'LOCATIONS_VIEW'; //The location_id is not updateable from this view (inserts will fail) COLUMN_NAME UPD ------------------------------ --- DEPARTMENT_ID YES DEPARTMENT_NAME YES LOCATION_ID NO CITY NO //Attempts at inserting/updating location_id will error out In the preceding example, the primary key index on the location_id column of the locations table is not unique in the locations_view view. Therefore, locations is not a key-preserved table and columns from that base table are not updatable. INSERT INTO locations_view VALUES (999, 'Entertainment', 87, 'Roma'); INSERT INTO locations_view VALUES * ERROR at line 1: ORA-01776: cannot modify more than one base table through a join view //department_id and department_name are updateable so DML operations will work You can insert, update, or delete a row from the departments base table, because all the columns in the view mapping to the departments table are marked as updatable and because the primary key of departments is retained in the view. INSERT INTO locations_view (department_id, department_name) VALUES (999, 'Entertainment'); 1 row created. This information is available via Oracle 10G documentation from Stanford University http://stanford.edu/dept/itss/docs/oracle/10g/server.101 Dziękuję Martin ______________________________________________ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Thu, 13 Nov 2008 20:17:09 -0100 > Subject: is INSERT into VIEW supported > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > > I have a VIEW that is defined over two base tables. One table is subtype > of another table and it's the VIEW that connects them. Now when I want to > insert into a subtable I have to insert through the VIEW. However I am > getting an error message when I try to insert into a VIEW. I found the > solution to this problem using the TRIGGER with "INSTEAD OF" but that was > from one of the Oracle discussions, and that works only with Oracle. > As far as I know MySQL does not support "INSTEAD OF", and my question is > does MySQL support INSERTS into VIEW? Or is there some way I can insert > into a view? > > Thanks > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > _________________________________________________________________ Stay up to date on your PC, the Web, and your mobile phone with Windows Live http://clk.atdmt.com/MRT/go/119462413/direct/01/
