List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:November 13 2008 8:02pm
Subject:RE: is INSERT into VIEW supported
View as plain text  
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,
   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
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: dzenan.causevic@stripped
> To: mysql@stripped
> 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:
> To unsubscribe:

Stay up to date on your PC, the Web, and your mobile phone with Windows Live
is INSERT into VIEW supporteddzenan.causevic13 Nov
  • RE: is INSERT into VIEW supportedMartin Gainty13 Nov