In the previous tutorial, we have seen database views and how to create views in MySQL. In this tutorial, we will see how to insert and update the data in the base table through views in MySQL. MySQL allows views not only to fetch the data but insert and update it too. Additionally, you can use the views to delete the rows in the base table.
However, there are some limitations in the views that don’t allow you to perform certain operations under certain conditions. But before that, let’s understand what updatable views are.
What Are Updatable Views?
As of now, you know that we can use views to create a virtual table and fetch data from multiple tables. Apart from that, views also allow you to insert, update and delete the data in the base table using the queries. But there should not be any of the following elements in the query to create an updatable view.
- Aggregate functions such as SUM, MIN, MAX etc.
- DISTINCT, GROUP BY, HAVING, UNION Clause.
- Subquery in the SELECT clause and Joins
- Multiple references to any column of base table
- Reference to non-updatable view in the FROM clause.
- Detailed Limitations on MySQL documentation.
Note that you can use inner joins sometimes to create updatable views based on multiple tables.
Prerequisites to Create Updatable Views?
Before creating updatable views, take care of the above points to avoid errors. To create the view, we will need a table with some data. For the sake of simplicity, we will not use multiple tables. As soon as you get the idea of how views are created, you can use multiple tables to create views.
So, we will create a table and insert data into it before creating updatable views.
Syntax to Create Updatable Views
The syntax of creating updatable views is the same as creating regular views. You can read how to create MySQL views here.
Syntax to create updatable views –
CREATE VIEW viewName
AS SELECT column_names
FROM table_namesCode language: SQL (Structured Query Language) (sql)Remember the limitations of updatable views listed above in the “what are updatable views” section.
Example to create updatable views
First, we will create a table named employee and insert data into it.
CREATE TABLE employees(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
deptId INT,
city VARCHAR(100),
phone VARCHAR(10)
);Code language: SQL (Structured Query Language) (sql)INSERT INTO employees(name,deptId,city,phone)
VALUES("John",1,"Mumbai","8987678987"),
("Raj",2,"Delhi","8976545678"),
("Gini",5,"Kolkata","9987876556"),
("Baki",3,"Bangaluru","7898789877"),
("Vicky",4,"Gurugram","8898789879");Code language: SQL (Structured Query Language) (sql)Let’s check if the data is inserted correctly in the table.
SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)
We will create a view of the name “emp_view,” which refers to the name, deptId, and city columns of the table “employees.”
CREATE VIEW emp_view
AS SELECT name, deptId, city
FROM employees;Code language: SQL (Structured Query Language) (sql)Our view is created successfully. Let’s check what data we have got inside the view table.
SELECT * FROM emp_view;Code language: SQL (Structured Query Language) (sql)
As you can see, the view is created successfully and shows us the correct data.
Update Data in Base Table Through View
Let’s try updating the data of view “emp_view” and check if the changes reflect in the base table “employees”.
UPDATE emp_view
SET deptId=2
WHERE name="Gini";Code language: SQL (Structured Query Language) (sql)Make sure you target the particular row using the WHERE clause. If you don’t use the WHERE clause, all rows will be updated at once.
As you can see, the output of the query shows “rows matched: 1” and “changed: 1” which means the update query was executed successfully. Let’s check if the row in the base table “employees” is updated or not.
SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)
As you can see, the deptId in the base table is updated from 5 to 2, which means our updatable view is working fine.
Delete Data in Base Table Through View
Now let’s try deleting data from the view and check if the data also gets deleted from the base table or not.
DELETE FROM emp_view
WHERE name="Gini";Code language: SQL (Structured Query Language) (sql)The delete query is executed successfully; let’s check the view table if the row is deleted.
SELECT * FROM emp_view;Code language: SQL (Structured Query Language) (sql)
The row from view is deleted; let’s check whether or not the same is reflected in the base table.
SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)
As you can see, the row from the base table is also deleted.
Conclusion
In this tutorial, we learned what an updatable view is, how to create it, and the example. You can create as many views as possible in your database and try operations on them. I hope you have understood how to create updatable views. You can also refer to the previous tutorial on how to create views in MySQL. See you in the following tutorial!
References
MySQL official documentation on updatable views.