Creating Updateable Views

Summary: in this tutorial, we will show you how to create an updateable view and update data in the underlying table through the view.

In MySQL, views are not only read-only but also updateable. However in order to create an updateable view, the SELECT statement that defines the view has to follow several following rules:

  • The SELECT statement must only refer to one database table.
  • The SELECT statement must not use GROUP BY or HAVING clause.
  • The SELECT statement must not use DISTINCT in the column list of the SELECT clause.
  • The SELECT statement must not refer to read-only views.
  • The SELECT statement must not contain any expression (aggregates, functions, computed columns…)

When you create updateable views, make sure that you follow the rules above.

Example of creating updateable view

Let’s practice with an example of creating an updateable view.

First, we create a view named officeInfo against the offices table. The view refers to three columns of the offices table:  officeCode, phone and city.

Next, we can query data from the officeInfo view using the SELECT statement.

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

Finally, to see the change, we can select the data from the officeInfo view by executing following query:

In this tutorial, we have shown you how to create an updateable view and how to update data in the underlying table through the view.