Updating Views in MySQL
mysql, sql-update, view
Solution
As documented under Updatable and Insertable Views:
Some views are updatable. That is, you can use them in statements such as `UPDATE`, `DELETE`, or `INSERT` to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. 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)
`DISTINCT`
`GROUP BY`
`HAVING`
`UNION` or `UNION ALL`
Subquery in the select list
Certain joins (see additional join discussion later in this section)
Nonupdatable view in the `FROM` clause
A subquery in the `WHERE` clause that refers to a table in the `FROM` clause
Refers only to literal values (in this case, there is no underlying table to update)
Uses `ALGORITHM = TEMPTABLE` (use of a temporary table always makes a view nonupdatable)
Multiple references to any column of a base table.
`[ deletia ]`
It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the `MERGE` algorithm. For this to work, the view must use an inner join (not an outer join or a `UNION`). Also, only a single table in the view definition can be updated, so the `SET` clause must name only columns from one of the tables in the view. Views that use `UNION ALL` are not permitted even though they might be theoretically updatable, because the implementation uses temporary tables to process them.
Problem
I am creating a view to show the user his/her data, but I also want the user to be able to make changes in some of the fields in those views. Are the changes made in a view reflected in the base table as well? Also, would I be able to update a view that is made up of more than one base table?