In this blog, we will study you how to generate an updatable view and update data in the basic table using the view.
Starter to MySQL updatable views
In MySQL, views are not only query-able but then again also updatable. It means that you can use the INSERT or UPDATE declaration to insert or update rows of the original table using the updatable view. In addition, you can practice DELETE statement to eliminate rows of the original table through the view.
Though, to generate an updatable view, the SELECT declaration that defines the view must not cover any of the subsequent elements:
Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
DISTINCT
GROUP BY clause.
HAVING clause.
UNION or UNION ALL clause.
Left join or outer join.
Subquery in the SELECT section or in the WHERE section that refers to the table looked in the FROM section.
Position to non-updatable view in the FROM clause.
Position only to literal values.
Multiple positions to any column of the base table.
If you create a view with the TEMPTABLE algorithm, you cannot bring up-to-date the view.
Note that it is occasionally possible to create updatable views created on multiple tables using an inner join.
MySQL updatable view example
Let’s generate an updatable view.
Primary, we create a view named office Info built on the offices table in the example database. The view states to three columns of the offices table:officeCode phone, and city.
Following, we can query data from the officeInfo view using the subsequent statement:
Before, we can alter the phone number of the office with officeCode 4 over the officeInfo view using the subsequent UPDATE statement.
In conclusion, to confirm the change, we can query the data as of the officeInfo view by performing the subsequent query:
Checking updatable view info
You can crisscross if a view in a database in updatable by querying the is_updatable column from the views table in the information_schema database.
The subsequent query gets all views from the classicmodels database and shows which views are updatable.
Eliminating rows through the view
First, we generate a table named items, insert few rows into the items table, and create a view that covers items whose prices are more than 700.
Next, we use the DELETE declaration to remove a row with id value 3.
MySQL returns a message saying that 1 row(s) affected.
Third, let’s check the data through the view again.
Fourth part, we can also request the data from the base table items to confirm if the DELETE declaration deleted the row.
As you get, the row with id 3 was removed from the original table.
In this blog, we have made known you how to create an updatable view and update data in the original table using the view.