mysql View's Method of Creating Updatable View

This article illustrates the method of creating updatable views for mysql views. Share it for your reference, as follows:

As we know, in mysql, views are not only queryable, but also updatable. This means that we can insert or update the rows of the base table through updatable views using the insert or update statements. In addition, we can use the delete statement to delete the rows of the underlying table through the view. However, to create an updatable view, the select statement that defines the view cannot contain any of the following elements:

Aggregate functions, such as min, max, sum, avg, count, etc. DISTINCT clause GROUP BY clause HAVING clause Left connection or outer connection. UNION or UNION ALL clause A subquery in the SELECT clause or a subquery in the where clause that references the table appears in the FROM clause. Reference to non-updatable views in the FROM clause Reference literal values only Multiple references to any column of the base table

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

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

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

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 to change the office phone number with an officeCode value of: 4 through the officeInfo view:

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

Finally, validate the changes and query the data in the officeInfo view by executing the following query:

mysql> SELECT
  *
FROM
  officeInfo
WHERE
  officeCode = 4;

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

After that, 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 for all the 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 views again. First, create a table named items, insert a few rows into the items table, and create a view that queries items with prices 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

Use the DELETE statement to delete the row with id of 3:

SELECT
  *
FROM
  officeInfo;

0

mysql returns 1 message indicating that 1 row is affected:

Query OK, 1 row affected

Let's examine the data through the view again:

SELECT
  *
FROM
  officeInfo;

1

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

SELECT
  *
FROM
  officeInfo;

2

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

Ok, that's all for this record.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Encyclopedia", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Encyclopedia" and "MySQL Common Functions Encyclopedia"

I hope this article is helpful to everyone's MySQL database.