MySQL – Updating Views

MySQL views are read-only by default, which means you cannot perform DML operations such as INSERT, UPDATE, or DELETE directly on them.

However, you can update the underlying tables on which the view is based, and the changes will be reflected in the view. The changes you make to an employee’s salary in the “employees” table will also be reflected in the “high_salary_employees” view.

The WITH CHECK OPTION clause can be used when creating a view to make it updatable. By using this method, you can specify conditions that must be satisfied in order for a row to be updated or inserted.

The following syntax is used to update the existing view in MySQL:

Syntax:

ALTER VIEW view_name AS    
SELECT columns    
FROM table    
WHERE conditions;    

Example

An example of how to create an updatable view that displays all employees with a salary greater than 10000 from the “employees” table is shown below:

CREATE OR REPLACE VIEW high_salary_employees 
AS SELECT first_name, last_name, salary FROM employees 
WHERE salary > 10000
WITH CHECK OPTION;

You can perform DML operations on the updatable view after it has been created.

UPDATE high_salary_employees SET salary = salary+1000;

It is important to note that while an updateable view allows you to perform DML operations, it does not guarantee that the operation will be successful. A change that violates the conditions specified in the WITH CHECK OPTION clause will result in the operation failing.

It is also not possible to create an updateable view on a table that already holds a foreign key reference to another table.

Additionally, if your underlying table is partitioned, a updateable  view cannot be created.