» MySQL Views » Creating Updateable Views

Creating Updateable Views

Views are not only read-only but also updateable. However in order to create an updateable view, the SELECT statement which defines View has to follow several following rules:

  • SELECT statement must not reference to more than one table. It means it must not contain more than one table in FROM clause, other tables in JOIN statement, or UNION with other tables.
  • SELECT statement must not use GROUP BY or HAVING clause.
  • SELECT statement must not use DISTINCT in the selection list.
  • SELECT statement must not reference to the view that is not updateable
  • SELECT statement must not contain any expression (aggregates, functions, computed columns…)

When you want to create updateable views, check all rules above or you can remember that data rows in the view must be one to one relationship with data rows in the underlying tables.

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


First you create a view against offices table which only exposes the officeCode, phone and city.

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices

The view is simple and followed all the rules of creating updateable view above.

Then you can retrieve data from the view officeInfo

SELECT * FROM officeInfo

You can change the phone number of office with officeCode is 4 through the view you've created.

UPDATE officeInfo
 SET phone = '+33 14 723 5555'
 WHERE officeCode = 4

And to see the change you can select changed office information from the view by executing following query:

SELECT * FROM officeInfo
WHERE officeCode = 4

In this tutorial, you've learned how to create updateable views and the rules to create them.

Search