Summary: in this tutorial, we will show you how to create an updateable view and update data in the underlying table through the view.
SELECTstatement must only refer to one database table.
SELECTstatement must not use GROUP BY or HAVING clause.
SELECTstatement must not use DISTINCT in the column list of the
SELECTstatement must not refer to read-only views.
SELECTstatement 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
CREATE VIEW officeInfo AS SELECT officeCode, phone, city FROM offices
Next, we can query data from the
officeInfo view using the
SELECT * FROM officeInfo
Then, we can change the phone number of the office with
officeCode 4 through the
officeInfo view by using the UPDATE statement.
UPDATE officeInfo SET phone = '+33 14 723 5555' WHERE officeCode = 4
Finally, to see the change, we can select the data from the
officeInfo view by executing following query:
SELECT * FROM officeInfo WHERE officeCode = 4
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.