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
Re: Updatable view using subquery??
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
Re: Updatable view using subquery??
On Mon, Feb 9, 2009 at 1:17 PM, blue.trapez...@gmail.com 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??
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
