MySQL – Update View

MySQL is a popular open-source relational database management system (RDBMS) that is usually used for developing scalable and high-performance databases. A VIEW serves as a virtual table that interacts with data derived from one or more underlying tables through a defined query.

In this article, We will learn about MySQL Update View Statement, and How to Update View in MySQL by understanding various examples and so on.

MySQL Update View Statement

• In relational database management systems (RDBMS) like MySQL, a VIEW is a virtual table interactive with data generated from one or more underlying tables through either a defined query.
• Unlike a regular table, the VIEW as a query doesn’t store the data itself. Instead, it creates a result set when someone queries it. The UPDATE VIEW Statement in MySQL is used to UPDATE an existing VIEW.
• Any changes made in the VIEW will also be reflected in the table. Not all VIEWS are updatable. While updating the VIEW there are certain things that we need to remember are defined below:
  • Single Table References: The VIEW must reference exactly one table. i.e. The SELECT statement defining the VIEW can only involve a single base table. No joins or subqueries involving multiple tables are allowed.
  • No Aggregates: The VIEWS having the aggregate functions like SUM(), COUNT(), AVG(), are updatable unless and until used with a GROUP BY clause.
  • No UNION or UNION ALL: UNION and UNION ALL Operations are not applicable to the SELECT statements.
  • No DISTINCT, GROUP BY, HAVING: The SELECT statement defining the VIEW cannot have DISTINCT, GROUP BY, HAVING clause.

Syntax:

UPDATE  view_name
SET  column1 = value1, column2 = value2 , . . . . , column_n = value_n
WHERE condition1 , condition2, . . . . , condition_n;

Explanation:

• view_name: The view you want to update.
• column: The column you want to update.
• value: The new value you want to set.
• WHERE: The rows which you want to update

It depends on the need of the user whether to use WHERE clause or not. If the WHERE clause is specified then only those rows will get updated which satisfies the conditions and if WHERE clause is not used then all the rows will get updated.

Let’s set up an Environment

Let’s take an example of the EMPLOYEE table having EMP_ID, NAME, AGE, and SALARY as columns.

CREATE TABLE EMPLOYEE (
    EMP_ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    SALARY INT
);

Insert the data on it:

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES
(1, 'Sahil', 21, 15000),
(2, 'Alen', 22, 13000),
(3, 'John', 22, 14000),
(4, 'Alex', 20, 13000),
(5, 'Mathew', 22, 14000),
(6, 'Sia', 21, 15000),
(7, 'David', 22, 16000),
(8, 'Tim', 21, 14000),
(9, 'Leo', 20, 15000),
(10, 'Tom', 21, 16000);

After Inserting the EMPLOYEE looks like:

EMPLOYEE Table

Let’s Creating Views

Let’s create a view named view1 that displays the EMP_ID and SALARY columns from the EMPLOYEE table.

Query:

CREATE VIEW view1 AS
SELECT EMP_ID, SALARY
FROM 
EMPLOYEE;

Output:

view1

Let’s create another view named view2 that displays the EMP_ID, AGE, and SALARY columns from the EMPLOYEE table.

Query:

CREATE VIEW view1 AS   
SELECT EMP_ID,AGE, SALARY   
FROM 
EMPLOYEE;

Output:

view2

How to Update View in MySQL?

To update a view in MySQL, we use the UPDATE statement with the view name, set the columns we want to update and use a WHERE clause to specify which rows to update.

Syntax:

UPDATE  view_name
SET  column1 = value1, column2 = value2 , . . . . , column_n = value_n
WHERE condition1 , condition2, . . . . , condition_n;

Example 1: Updating a VIEW Using WHERE Clause

Let’s Update the SALARY column in the view named view1 to 10000 for the employee with EMP_ID equal to 2.

Query:

UPDATE view1
SET SALARY=10000
WHERE EMP_ID=2;

Output:

Updated view

Table After Perfroming the UPDATE View Operation:

EMPLOYEE Table

Explaination:

• In the above example, we are updating the VIEW and changing the SALARY of employee to 10000 whose EMP_ID = 2. Here we are using WHERE clause specify the condition.
• As we are using WHERE clause so only the selective records are updated in the VIEW. Here we can see that the changes made in view1 are also reflected in EMPLOYEE table.

Example 2: Updating a VIEW Without Using WHERE Clause

Let’s Update the SALARY column in the view named view1 to 15000 for all employees.

Query:

UPDATE view1 
SET SALARY = 15000;

Output:

view2

Table After Perfroming the UPDATE View Operation:

EMPLOYEE1

Explaination:

• In the above example, we are updating the VIEW and changing the SALARY of employees to 15000. As we are not using WHERE clause so so all the records in the view2 are updated.
• Here we can see that the changes made in view2 are also reflected in EMPLOYEE table. All the employees SALARY is updated to 15000.

Conclusion

Overall, MySQL provides a feature to update the VIEW. There are some restrictions to UPDATE the VIEW such as the view referencing only a single table can be updated, the view while creating should not have the aggregate functions like SUM(), COUNT(), AVG(), etc. It is not compulsory to use WHERE clause as it depends on the user whether to use WHERE clause or not.

Using WHERE clause will UPDATE only the selective records whereas not using WHERE clause will UPDATE all the records. Updating the VIEW will also UPDATE the data in the table.