Summary: in this tutorial, we will show you how to create an updatable view and update data in the underlying table through the view.
Introduction to MySQL updatable views
In MySQL, views are not only queryable but also updatable. This implies that you can use the INSERT or UPDATE statement to add or modify rows of the base table through the updatable view.
Additionally, you can use the DELETE statement to remove rows of the underlying table via the view.
However, to create an updatable view, the SELECT statement defining 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 non-updatable views in the
FROM
clause. - Use literal values.
- Multiple references to any column of the base table.
If you create a view with the TEMPTABLE algorithm, the view is not updatable.
Note that it is possible to create updatable views based on multiple tables using an inner join.
MySQL updatable view example
Let’s create an updatable view.
First, create a view named officeInfo
based on the offices
table in the sample database. The view refers to three columns of the offices
table: officeCode
, phone
, and city
.
CREATE VIEW officeInfo
AS
SELECT officeCode, phone, city
FROM offices;
Code language: SQL (Structured Query Language) (sql)
Second, retrieve data from the officeInfo
view using the following statement:
SELECT * FROM officeInfo;
Code language: SQL (Structured Query Language) (sql)
Third, change the phone number of the office with officeCode
4 through the officeInfo
view using the following UPDATE statement.
UPDATE officeInfo
SET
phone = '+33 14 723 5555'
WHERE
officeCode = 4;
Code language: SQL (Structured Query Language) (sql)
Finally, query the data from the officeInfo
view to verify the change:
SELECT
*
FROM
officeInfo
WHERE
officeCode = 4;
Code language: SQL (Structured Query Language) (sql)
Checking updatable view information
You can check if a view in a database is updatable by querying the is_updatable
column from the views table in the information_schema
database.
The following query gets all views from the classicmodels database and shows which views are updatable.
SELECT
table_name,
is_updatable
FROM
information_schema.views
WHERE
table_schema = 'classicmodels';
Code language: SQL (Structured Query Language) (sql)
Removing rows through the view
First, create a table named items, insert some rows into the items table, and create a view that contains items whose prices are greater than 700.
-- create a new table named items
CREATE TABLE items (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(11 , 2 ) NOT NULL
);
-- insert data into the items table
INSERT INTO items(name,price)
VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.50) ;
-- create a view based on items table
CREATE VIEW LuxuryItems AS
SELECT
*
FROM
items
WHERE
price > 700;
-- query data from the LuxuryItems view
SELECT
*
FROM
LuxuryItems;
Code language: SQL (Structured Query Language) (sql)
Second, use the DELETE
statement to remove a row with id value 3.
DELETE FROM LuxuryItems
WHERE id = 3;
Code language: SQL (Structured Query Language) (sql)
MySQL returns a message saying that 1 row(s) is affected.
Third, check the data through the view again:
SELECT
*
FROM
LuxuryItems;
Code language: SQL (Structured Query Language) (sql)
Finally, query the data from the base table items to verify if the DELETE
statement deleted the row.
SELECT
*
FROM
items;
Code language: SQL (Structured Query Language) (sql)
The output shows that the row with id 3 was removed from the base table.
In this tutorial, you have learned how to create an updatable view and modify data in the underlying table through the view.