View is not insertable, if it contains sub query in FROM clause

Question

I have a mysql view which has sub query in FROM clause. This view is updatable. I tried updating single table and it worked fine. But I am not able to insert into that table. It says :

Error Code: 1471. The target table action_view of the INSERT is not insertable-into

mysq view :

    CREATE OR REPLACE
VIEW `action_view` AS
    SELECT 
        `ca`.`id` AS `id`,
        `cah`.`title` AS `title`,
        `ca`.`idCareActionHead` AS `idCareActionHead`,
        `ca`.`idPeople` AS `idPeople`,
        `ca`.`assignedTo` AS `assignedTo`,
        `ca`.`dueDate` AS `dueDate`,
        `note`.idCareAction AS `idCareAction`
    FROM
       `care_action` `ca`
        JOIN `care_action_head` `cah`
        JOIN `people` `p`
        JOIN (SELECT 
            `cn`.`idCareAction` AS `idCareAction`
        FROM `care_note` `cn`) `note`

    WHERE
        `ca`.`idCareActionHead` = `cah`.`id`
            AND (`ca`.`idPeople` = `p`.`id`)
            AND (`note`.`idCareAction` = `ca`.`id`)

update query which works fine :

update action_view set idCareActionHead = 1 where action_view.id =25;

Insert query which gives the above mentioned error :

insert into action_view (idCareActionHead, idPeople) values (12, 4);

I have referred the latest mysql docs

https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html1 which talks about sub queries within the SELECT statement, but does not say anything regarding sub queries in FROM clause.

My question is, is it possible to insert into view which has sub query in FROM clause in mysql or am I doing anything wrong here?

Answer 1

First of all, I do not understand why you use a subquery in the from clause at all. There is no filtering, grouping, formatting of data in the subquery, you just select a single field from a table. You can simply join on the care_note table directly.

However, more importantly MySQL applies a very sensible restriction on inserts into views:

With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

...

• The view must contain all columns in the base table that do not have a default value.

...

This restriction fails for the note subquery, resulting in the error message you received.

UPDATE

Reflecting on the comment by the OP below that the subquery does contain group by and aggregate functions - the same MySQL documentation referenced above also says:

To be more specific, a view is not updatable if it contains any of the following:

• Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

• ...

• GROUP BY

...

This means that the subquery is definitely not updateable. This also means that the view cannot be insertable, but its other parts can still be updateable.