MySQL Updatable Views

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.

Was this tutorial helpful?