Subscribe to MySQL Tutorial site RSS Feed
Skip Navigation Links » MySQL Views » Creating Updateable Views

Creating Updateable Views

Views are not only read-only but also can be updaeable. However to create an updateable view, the SELECT statement which defined view has to follow some 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 updatable
  • SELECT statement must not contain any expression (aggregates, functions, computed columns…)

When you want to create updatable 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 table.

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 following all above rules of creating updateable view above.

Now, you can retrieve data from the view officeInfo

SELECT * FROM officeInfo

We change the phone number of office with officeCode is4

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