MySQL - Views Part 4: Updating Data Through Views

In MySQL, you can update the underlying tables using certain views if they meet specific criteria. A view must not include joins, aggregations, or derived columns.

Example 1: Updatable View

CREATE VIEW BasicInfo AS

SELECT id, name, salary

FROM employees;

You can update data using this view:

UPDATE BasicInfo

SET salary = salary + 5000

WHERE id = 1;

Example 2: Non-Updatable View

A view containing joins is read-only:

CREATE VIEW CombinedInfo AS

SELECT e.name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.id;

Example 3: Checking Updatable Views

SELECT TABLE_NAME, IS_UPDATABLE

FROM INFORMATION_SCHEMA.VIEWS;

Explanation:

Updatable views provide flexibility for manipulating underlying data, but certain restrictions apply.