Hi! On Mon, Feb 9, 2009 at 7:17 AM, <blue.trapez...@gmail.com> 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
