Detailed explanation of how to create an updateable view in MySQL

This article uses an example to describe how to create an updateable view in MySQL. Share with you for your reference, the details are as follows:

We know that in MySQL, views are not only queryable but also updatable. This means that we can use INSERT or UPDATE statements to insert or update rows of the base table through an updatable view. In addition, we can also use the delete statement to delete the rows of the underlying table through 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, count, etc.
• DISTINCT Clause
• GROUP BY Clause
• HAVING Clause
• Left join or outer join.
• UNION or UNION ALL Clause
• A subquery in the SELECT clause or a subquery in a where clause that refers to the table appears in the FROM clause.
• Referencing a non-updatable view in the FROM clause
• Quote only literal values
• Multiple references to any column of a base table

If we use the temptable algorithm to create a view, we cannot update the view, but sometimes we can use inner joins to create an updateable view based on multiple tables. Without further ado, let's take a look at how to create an updateable view. Let's first try to create a view called officeInfo based on the offices table, which refers to three columns in the offices table: officeCode, phone and city:

CREATE VIEW officeInfo
 AS
  SELECT officeCode, phone, city
  FROM offices;

Next, use the following statement to query data from the officeInfo view:

SELECT
  *
FROM
  officeInfo;

Execute the above query statement and get the following results:

mysql> SELECT * FROM officeInfo;
+------------+------------------+---------------+
| officeCode | phone | city |
+------------+------------------+---------------+
| 1 | +1 650 219 4782 | San Francisco |
| 2 | +1 215 837 0825 | Boston |
| 3 | +1 212 555 3000 | NYC |
| 4 | +33 14 723 4404 | Paris |
| 5 | +86 33 224 5000 | Beijing |
| 6 | +61 2 9264 2451 | Sydney |
| 7 | +44 20 7877 2041 | London |
+------------+------------------+---------------+
7 rows in set

Then, use the following update statement through the officeInfo view to change the officeCode value to 4, the office telephone number:

UPDATE officeInfo
SET
  phone = '+86 089866668888'
WHERE
  officeCode = 4;

Finally, verify the changes by querying the data in the officeInfo view:

mysql> SELECT
  *
FROM
  officeInfo
WHERE
  officeCode = 4;

+------------+------------------+-------+
| officeCode | phone | city |
+------------+------------------+-------+
| 4 | +86 089866668888 | Paris |
+------------+------------------+-------+
1 row in set

We can check whether the views in the database are updatable by querying the is_updatable column from the views table in the information_schema database. For example, let's query the luyaran database to get all views and show which views are updatable:

SELECT
  table_name, is_updatable
FROM
  information_schema.views
WHERE
  table_schema = 'luyaran';

Execute the above query statement and get the following results:

+------------------+--------------+
| table_name | is_updatable |
+------------------+--------------+
| aboveavgproducts | YES |
| bigsalesorder | YES |
| customerorders | NO |
| officeinfo | YES |
| saleperorder | NO |
+------------------+--------------+
5 rows in set

Let's try to delete rows through a view. First, create a table called items, insert some rows into the items table, and create a view that queries items with a price greater than 700:

USE testdb;
-- 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;

After executing the above query statement, the following results are obtained:

+----+--------+--------+
| id | name | price |
+----+--------+--------+
| 1 | Laptop | 700.56 |
| 3 | iPad | 700.5 |
+----+--------+--------+
2 rows in set

After that, use the DELETE statement to delete the row with id 3:

DELETE FROM LuxuryItems
WHERE
  id = 3;

mysql returns a message indicating that 1 row was affected:

Query OK, 1 row affected

Let's check the data again through the view:

mysql> SELECT * FROM LuxuryItems;
+----+--------+--------+
| id | name | price |
+----+--------+--------+
| 1 | Laptop | 700.56 |
+----+--------+--------+
1 row in set

We can also query data from the base table items to verify that the DELETE statement actually deleted the row:

mysql> SELECT * FROM items;
+----+---------+--------+
| id | name | price |
+----+---------+--------+
| 1 | Laptop | 700.56 |
| 2 | Desktop | 699.99 |
+----+---------+--------+
2 rows in set

We can see that the row with ID 3 is deleted in the base table.

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.