Updating Views in MySQL

Question

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?

Answer 1

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.