Updatable views and limitations in SQL

Introduction

Updatable views let you change data through a saved query, making it easier to work with complex data without changing the original tables directly.

You want to simplify complex table joins for users.

You want to restrict access to certain columns or rows while still allowing updates.

You want to present data in a specific way but still allow changes.

You want to reuse a common query and allow edits through it.

Syntax

SQL

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Not all views can be updated. The view must follow certain rules.

Updatable views usually involve a single table without complex joins or aggregations.

Examples

A simple view showing active employees. This view can be updated if it meets rules.

SQL

CREATE VIEW simple_view AS
SELECT id, name, age
FROM employees
WHERE active = 1;

A view joining two tables. This view is usually NOT updatable.

SQL

CREATE VIEW joined_view AS
SELECT e.id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Sample Program

This example creates a table and a view of active employees. It updates the age of Alice through the view and then shows the updated data in the original table.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  active BOOLEAN
);

INSERT INTO employees VALUES (1, 'Alice', 30, TRUE), (2, 'Bob', 25, FALSE);

CREATE VIEW active_employees AS
SELECT id, name, age
FROM employees
WHERE active = TRUE;

-- Update through the view
UPDATE active_employees SET age = 31 WHERE id = 1;

-- Check the update
SELECT * FROM employees WHERE id = 1;

Important Notes

Views with joins, GROUP BY, DISTINCT, or aggregate functions are usually not updatable.

Some databases allow triggers or rules to make complex views updatable, but this is advanced.

Always test if your view is updatable by trying an UPDATE or INSERT through it.

Summary

Updatable views let you change data through a saved query.

Simple views on one table without complex operations are usually updatable.

Complex views with joins or aggregates are not updatable by default.