List:General Discussion« Previous MessageNext Message »
From:blue.trapezius Date:February 9 2009 12:17pm
Subject:Updatable view using subquery??
View as plain text  
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

Thread
Updatable view using subquery??blue.trapezius9 Feb
  • Re: Updatable view using subquery??Baron Schwartz9 Feb
  • Re: Updatable view using subquery??Jochem van Dieten9 Feb
    • Re: Updatable view using subquery??Baron Schwartz9 Feb