Updating Views in SQL | Rules, Limitations & Examples

Updating Views

Views are often introduced as read-only virtual tables, which leads many beginners to assume that views cannot be used to modify data. In reality, this is only partially true.

Some views can be updated, while others cannot.

Understanding when a view is updatable and how updates affect base tables is essential for working with real-world databases.

This chapter explains how updatable views work, their rules, limitations, and practical use cases.

What Is an Updatable View?

An updatable view is a view that allows:

INSERT

UPDATE

DELETE

operations to be performed on it.

When you modify data through an updatable view:

The changes are applied to the underlying base table

The view itself still does not store any data

In simple words:

You update the view, The table gets updated

Why Updatable Views Are Useful

Updatable views are commonly used to:

Simplify data modification logic

Restrict access to certain columns

Control which rows can be modified

Enforce business rules at the database level

They are especially helpful in large systems where:

Users should not access base tables directly

Only limited updates should be allowed

Example

CREATE VIEW active_employees AS
SELECT emp_id, emp_name, salary, department_id
FROM employees
WHERE status = 'ACTIVE';

This view:

Is based on a single table

Uses no aggregation or joins

Is updatable

Performing UPDATE on an Updatable View

UPDATE active_employees
SET salary = salary + 3000
WHERE emp_id = 102;

The salary is updated in the employees table

The view reflects the updated data automatically

INSERT Using an Updatable View

INSERT INTO active_employees (emp_id, emp_name, salary, department_id, status)
VALUES (201, 'Rahul', 45000, 3, 'ACTIVE');

The row is inserted into the base table, not the view.

DELETE Using an Updatable View

DELETE FROM active_employees
WHERE emp_id = 201;

The row is removed from the base table

The view updates automatically

Rules for a View to Be Updatable

A view is generally updatable if:

It is based on only one table

It does not use:

1. GROUP BY

2. HAVING

3. Aggregate functions (SUM, AVG, etc.)

4. DISTINCT

5. Set operators (UNION, INTERSECT)

It does not contain subqueries in the SELECT list, If any of these are present, the view becomes read-only.

Using WITH CHECK OPTION

Enforcing View Conditions During Updates

By default, SQL allows updates that may cause rows to disappear from the view. To prevent this, we use WITH CHECK OPTION.

Example: Restricting Updates with CHECK OPTION

CREATE VIEW high_salary_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 60000
WITH CHECK OPTION;

Now:

You cannot insert a row with salary ≤ 60000

You cannot update salary below 60000

This ensures data consistency.

Why WITH CHECK OPTION Is Important

Without it:

Rows may be updated in a way that violates view conditions

Data can silently disappear from the view

With it:

All modifications must satisfy the view’s WHERE clause

Updatable Views with Joins

Views created using joins are usually not updatable.

CREATE VIEW employee_details AS
SELECT e.emp_id, e.emp_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

This view is typically read-only, because:

SQL cannot decide which table to update (Some databases allow limited updates, but this is DB-specific.)

Two Minute Drill

Views can be updatable or read-only
Updatable views allow INSERT, UPDATE, DELETE
Changes affect base tables
Sigle-table views are usually updatable
Aggregates and joins make views read-only
WITH CHECK OPTION enforce view conditions