MySQL Updatable Views – Complete Reference for Beginners

Mysql Updatable Views

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.

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_names
Code 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)
Employees Table Data
Employees Table Data

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)
Emp_view Data
Emp_view View Data

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.

Update View Data
Update View Data

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)
Employees Table Data After Update
Employees Table Data After Update

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)
Delete Data From View
Delete Data From View

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)
Employees Table Data After Delete
Employees Table Data After Delete

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.