List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:February 9 2009 4:37pm
Subject:Re: Updatable view using subquery??
View as plain text  
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/
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