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?

Solution

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.

Comments

[Amit Chigadani]

The sub query that I have shown is a simplified version. It does have group by and aggregate columns. And also the insert is inserting all the columns which doesn't have default value. And all those columns are part of Select clause in the view.

[Shadow]

If the subquery has group by and aggregate functions then it is definitely not insertable. The subquery is not even updateable, but other parts of the view can be updated. Btw, next time pls share all the relevant information to avoid such extra loops.

[Amit Chigadani]

Ya that could be true. But the simplified view that I have shown here still fails to insert. I don't get what is wrong with the simplified care_note sub query here.

[Amit Chigadani]

Let me be more clear here. My actual view has the group by or aggregate columns in the sub query. But my question was just w.r.t to the view that I have mentioned in my question. When you gave a work round to not have the care_note as part of sub query, I replied saying that my sub query has group by or aggregate cols. But question still stands only for the view that I have shown in my question. Sorry for any confusion here.

[Amit Chigadani]

And in your original answer you said This restriction fails for the note sub query, resulting in the error message you received. Can you please explain this little more. I assume you had written this w.r.t the actual question and not w.r.t my first comment.

[Shadow]

That note still applies. In order for a view to be insertable, all of its parts must be updatable. Your subquery is not updateable, therefore the view is not insertable, but partially updateable.

[Amit Chigadani]

Thank you. Now I understood it clearly. note is not updatabale, hence the view is not insertable. Thanks for your time @Shadow.