Creating 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 query-able but also updatable. It means that you can use the INSERT or UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.

However, to create an updatable view, the SELECT statement that defines the view must not contain any of the following elements:

If you create a view with the TEMPTABLE algorithm, you cannot update the view.

Note that it is sometimes possible to create updatable views based on multiple tables using an inner join.

MySQL updatable view example

Let’s create an updatable view.

First, we 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)

Next, we can query data from the officeInfo view using the following statement:

SELECT
*
FROM
officeInfo;
Code language: SQL (Structured Query Language) (sql)
mysql updateable view example

Then, we can 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, to verify the change, we can query the data from the officeInfo  view by executing the following query:

SELECT
*
FROM
officeInfo
WHERE
officeCode = 4;
Code language: SQL (Structured Query Language) (sql)
mysql updateable view example with officeInfo View

Checking updatable view information

You can check if a view in a database in 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)
updatable views information_schema

Removing rows through the view

First, we 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)
luxuryitems view

Second, we 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) affected.

Third, let’s check the data through the view again.

SELECT
*
FROM
LuxuryItems;
Code language: SQL (Structured Query Language) (sql)
MySQL DELETE through View

Fourth, we can also query the data from the base table items to verify if the DELETE statement actually deleted the row.

SELECT
*
FROM
items;
Code language: SQL (Structured Query Language) (sql)
Items table

As you see, the row with id 3 was removed from the base table.

In this tutorial, we have shown you how to create an updatable view and update data in the underlying table through the view.