MySQL - updatable vs. non-updatable view

1. Updatable Views

An updatable view allows you to insert, update, or delete rows in the underlying base table through the view.

Conditions for a View to be Updatable

  1. Must refer to only one table.

  2. Must not contain:

    • GROUP BY or DISTINCT

    • Aggregate functions (SUM, COUNT, etc.)

    • UNION or JOIN

    • Subqueries in the SELECT list

  3. Must include the primary key of the underlying table (for updates and deletes).

Example

Suppose we have a table employees:

emp_id name department salary
1 Alice HR 5000
2 Bob IT 6000

Create an updatable view:

CREATE VIEW it_employees AS
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT';

Update through the view:

UPDATE it_employees
SET salary = 6500
WHERE emp_id = 2;

  • The underlying employees table is updated: Bob’s salary becomes 6500.

2. Non-Updatable Views

A non-updatable view cannot be used to modify the underlying table. You can only SELECT data from it.

Reasons a View is Non-Updatable

  1. Uses JOINs to combine multiple tables.

  2. Includes GROUP BY, DISTINCT, or aggregates (SUM, AVG).

  3. Uses UNION or subqueries in the SELECT list.

  4. References a derived column (e.g., salary * 1.1 AS new_salary).

Example

CREATE VIEW avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

  • This view is non-updatable because it uses AVG() and GROUP BY.

  • You cannot do UPDATE avg_salary SET avg_salary = 6000;

3. Quick Comparison

Feature Updatable View Non-Updatable View
Can perform INSERT ✅ Yes ❌ No
Can perform UPDATE ✅ Yes ❌ No
Can perform DELETE ✅ Yes ❌ No
Uses JOIN / Aggregates ❌ No ✅ Yes
Use case Simplify queries & allow modifications Reporting, analytics, or aggregation

4. Key Tips

  • Always include primary key in the view for updates/deletes.

  • Non-updatable views are still useful for reports, calculations, and security.

  • MySQL will throw an error if you try to modify a non-updatable view.