MySQL updatable/Insertable views

 

In MySQL, views are not only query-able but also updatable. It means that you can use the INSERTor UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.

However, to create an updatable view, the SELECT statement that defines the view must not contain any of the following 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 clause or in the WHERE clause that refers to the table appeared in the FROM clause.
  • Reference to non-updatable view in the FROM clause.
  • Reference only to literal values.
  • Multiple references to any column of the base table.

If you create a view with the TEMPTABLE algorithm, you cannot update the view.

Note that it is sometimes possible to create updatable views based on multiple tables using an inner join.

Updatable view example

Let’s create an updatable view.

First, we create a view named officeInfo  based on the offices  table below

 

The view refers to three columns of the offices  table:officeCode phone,  and city.

 

Next, let query data from the officeInfo view

 

Then, we can change the phone number of the office with officeCode  4 through the officeInfo view using the following UPDATE statement.

 

To verify the change

You can use the same approach to Insert and remove row

Removing rows through the view

You can run also a simpleDELETE statement to remove a row with id value 4.

Inserting rows through the view

Checking updatable view information

You can check if a view in a database in updatable by querying the is_updatable column from the views table in the information_schema database.

The following query gets all views from the test_view database .

Mikael HOUNDEGNON
Author: Mikael HOUNDEGNON

My name is Mikael HOUNDEGNON. I am an experienced MySQL DBA/Developer based in the greater Chicago area. You can find out more about me here. I blog here mostly about things I don’t want to forget ? most likely, MySQL Tips. My specialties : MySQL Replication (Master Slave, MultiMaster, Fail over, etc) MySQL Backups MySQL Query Optimization MySQL Performance Tuning MySQL Stored Procedures Storage Engine Tuning Do you have an interesting project idea? Or you just want to chat? Get in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *