Re: Updatable view using subquery??
> Your subquery is not in the select list, it is in the where. A > subquery in the select list would be: > CREATE VIEW v_aa AS > SELECT > * > , (SELECT MAX(x) FROM y) AS z > FROM flight > > This is not updatable because there is no sensible way to propagate > changes to the y base table. Great catch :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updatable view using subquery??
On Mon, Feb 9, 2009 at 1:17 PM, wrote: > mysql> CREATE VIEW v_aa AS >-> SELECT * >-> FROM flight AS f >-> WHERE f.RouteID IN >-> (SELECT r.RouteID >-> FROM route AS r >-> WHERE r.To= >-> (SELECT a.AirportID >-> FROM airport AS a >-> WHERE a.AirportCode='SIN') >-> ) >-> ORDER BY FlightID DESC; > Query OK, 0 rows affected (0.02 sec) > > mysql> insert into v_aa >-> values (1,1141,3145); > Query OK, 1 row affected (0.00 sec) > > But according to the MySQL manual, "a view is not updatable if it contains > any of the following:...subquery in the select list". Your subquery is not in the select list, it is in the where. A subquery in the select list would be: CREATE VIEW v_aa AS SELECT * , (SELECT MAX(x) FROM y) AS z FROM flight This is not updatable because there is no sensible way to propagate changes to the y base table. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updatable view using subquery??
Hi! On Mon, Feb 9, 2009 at 7:17 AM, wrote: > Hi > > I am able to create an updatable view using a subquery in MySQL 5.1.29 > > mysql> CREATE VIEW v_aa AS >-> SELECT * >-> FROM flight AS f >-> WHERE f.RouteID IN >-> (SELECT r.RouteID >-> FROM route AS r >-> WHERE r.To= >-> (SELECT a.AirportID >-> FROM airport AS a >-> WHERE a.AirportCode='SIN') >-> ) >-> ORDER BY FlightID DESC; > Query OK, 0 rows affected (0.02 sec) > > mysql> insert into v_aa >-> values (1,1141,3145); > Query OK, 1 row affected (0.00 sec) > > But according to the MySQL manual, "a view is not updatable if it contains > any of the following:...subquery in the select list". I am quite confused by > this. Can someone help me understand the details of this? It's possible that restriction has been limited. But I wonder if you have some typo, like you've created the view with a different definition and you only think it has the definition listed above. I don't think that restriction has been changed; that would be a major change to the view code. So -- double-check and make sure. On a different note, you may want to rewrite that query so it doesn't use IN() subqueries, or it will perform very badly on larger datasets. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Updatable view using subquery??
Hi I am able to create an updatable view using a subquery in MySQL 5.1.29 mysql> CREATE VIEW v_aa AS -> SELECT * -> FROM flight AS f -> WHERE f.RouteID IN -> (SELECT r.RouteID -> FROM route AS r -> WHERE r.To= -> (SELECT a.AirportID -> FROM airport AS a -> WHERE a.AirportCode='SIN') -> ) -> ORDER BY FlightID DESC; Query OK, 0 rows affected (0.02 sec) mysql> insert into v_aa -> values (1,1141,3145); Query OK, 1 row affected (0.00 sec) But according to the MySQL manual, "a view is not updatable if it contains any of the following:...subquery in the select list". I am quite confused by this. Can someone help me understand the details of this? TIA
