On Mon, Feb 9, 2009 at 1:17 PM, <blue.trapezius@stripped> 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/
