Update View in MySQL

There are certain conditions that need to be satisfied to update a view. If any one of these conditions is not met, then we are not allowed to update the view.

1. The View should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.
2. The View should not be created using nested queries or complex queries.
3. The View should have all NOT NULL values.
4. The SELECT statement should not have the DISTINCT keyword.
5. The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.

1. Update View Definition/Structure

To update the view for adding or remove columns and rows by changing WHERE clause condition, we can use CREATE OR REPLACE VIEW statement.

Syntax:

CREATE OR REPLACE VIEW <view_name> AS

SELECT <column1>, <column2>, ………, <columnN>

FROM <table_name>

WHERE [condition];

Example:

Suppose we want to update the view IITHyderabadStudentsView we created above and delete the column sid from this view from StudentDetails table, we can do this as follows:

CREATE OR REPLACE VIEW IITHyderabadStudentsView AS
SELECT sname, age 
FROM StudentDetails
WHERE university = "IIT Hyderabad";

Output:

2. Insert Into View

To insert the new row into the view, we can do it in a similar way just like how we do it for normal tables.

Syntax:

INSERT INTO <view_name>(<column1>, <column2>, <column3>,.........) 
VALUES(<value1>, <value2>, <value3>,...........);

Example:

Let us insert a new row in the view IITHyderabadStudentsView which we have created above in the example of “Create View-based On Single Table”.

INSERT INTO IITHyderabadStudentsView(sid, sname, age)
VALUES(7, "Tenali Rama", 26);

SELECT * FROM IITHyderabadStudentsView;
SELECT * FROM StudentDetails;

Output:

You can see from the image below that inserting a row into view inserted the row into our original table as well. To see the changes, you can query the data from original table before and after insertion into the view.

Output:

3. Delete From View

To insert the new row into the view, we can do it in a similar way just like how we do it for normal tables. The syntax is:

DELETE FROM <view_name> WHERE [condition];

Example:

Let us delete a row in the view IITHyderabadStudentsView which we have created above in the example of “Create View-based On Single Table”. Let us delete the details of student whose name is “Tenali Rama”.

DELETE FROM IITHyderabadStudentsView WHERE sname = "Tenali Rama";

Output:

The data in the view before deletion is performed.

The data in the view after deletion is performed.

MySQL is an open-source RDBMS, i.e. Relational Database Management System which is maintained by Oracle. MySQL has support for major operating systems like Windows, MacOS, Linux, etc. MySQL makes it easy for users to interact with your relational databases, which store data in the form of tables. You can write SQL queries to get the required data from the databases using MySQL.

In this article, we will look at “Views in MySQL”, which act as virtual tables, and understand its advantages, as well as the syntax of Views for creation, updation, and deletion, with the help of awesome examples.