MySQL views are not only query-able but also Updatable.

In MySQL you can use the INSERT or UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.

However, to create an updatable view, the SELECT statement that defines the view must not contain any of the following elements:

• Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
• DISTINCT
• GROUP BY clause.
• HAVING clause.
• UNION or UNION ALL clause.
• Left join or outer join.
• Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.
• Reference to non-updatable view in the FROM clause.
• Reference only to literal values.
• Multiple references to any column of the base table.

If you create a view with the TEMPTABLE algorithm, you cannot update the view.

Example:

To create table

create table employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
dept varchar(10),
salary int(10)
);

To insert values

insert into employee values(100,'Thomas','Sales',5000);
insert into employee values(200,'Jason','Technology',5500);
insert into employee values(300,'Mayla','Technology',7000);
insert into employee values(400,'Nisha','Marketing',9500);
insert into employee values(500,'Randy','Technology',6000);

Output of above SQL Query

To create View

CREATE VIEW empInfo
 AS 
   SELECT id,name
   FROM employee;

Query data from the view

SELECT*
FROM empInfo;

Output of above query

Update the underlying table through the view

UPDATE empInfo 
SET 
    name = 'Thanusan'
WHERE
    id = 500;

Query data from empInfo after update row in view (empInfo)

Query data from employee after update row in view (empInfo)

Delete the underlying table row through the view

DELETE FROM empinfo 
WHERE
    id = 400;

Query data from empInfo after delete row in view (empInfo)

Query data from employee after delete row in view (empInfo)

Insert row in underlying table through the view

insert into empInfo 
values(600,'Sindujan');

Query data from empInfo after insert row in view (empInfo)

Query data from employee after insert row in view (empInfo)

In this tutorial, I have shown you how to create an updatable view and update data in the underlying table through the view.