From: Martin Gainty Date: November 13 2008 8:02pm Subject: RE: is INSERT into VIEW supported List-Archive: http://lists.mysql.com/mysql/215203 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_fb0df7f8-044c-4382-ae56-bfda6b2fd6d3_" --_fb0df7f8-044c-4382-ae56-bfda6b2fd6d3_ Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable 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=2C d.department_name=2C l.location_id=2C l.city FROM departments d=2C locations l WHERE d.location_id =3D l.location_id=3B //find out which columns are updateable from the view SELECT column_name=2C updatable=20 FROM user_updatable_columns WHERE table_name =3D 'LOCATIONS_VIEW'=3B //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=2C the primary key index on the location_id column= of the locations table is not unique in the locations_view view. Therefore= =2C locations is not a key-preserved table and columns from that base table= are not updatable. INSERT INTO locations_view VALUES (999=2C 'Entertainment'=2C 87=2C 'Roma')=3B 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 w= ork You can insert=2C update=2C or delete a row from the departments base table= =2C because all the columns in the view mapping to the departments table ar= e marked as updatable and because the primary key of departments is retaine= d in the view. INSERT INTO locations_view (department_id=2C department_name) VALUES (999=2C 'Entertainment')=3B 1 row created. This information is available via Oracle 10G documentation from Stanford Un= iversity http://stanford.edu/dept/itss/docs/oracle/10g/server.101 Dzi=EAkuj=EA Martin=20 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > Date: Thu=2C 13 Nov 2008 20:17:09 -0100 > Subject: is INSERT into VIEW supported > From: dzenan.causevic@stripped > To: mysql@stripped >=20 > 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=2C and that works only with Oracle. > As far as I know MySQL does not support "INSTEAD OF"=2C and my question i= s > does MySQL support INSERTS into VIEW? Or is there some way I can insert > into a view? >=20 > Thanks >=20 >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped= om >=20 _________________________________________________________________ Stay up to date on your PC=2C the Web=2C and your mobile phone with Windows= Live http://clk.atdmt.com/MRT/go/119462413/direct/01/= --_fb0df7f8-044c-4382-ae56-bfda6b2fd6d3_--