Updatable views in MySQL

Introduction

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

You want to simplify data access for users by showing only certain columns or rows.

You need to allow updates on a subset of data without exposing the full table.

You want to hide complex joins but still allow data changes.

You want to enforce security by restricting which data can be changed.

You want to keep your main tables safe but allow controlled updates.

Syntax

MySQL

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

The view must be based on a single table or a join that supports updates.

Not all views are updatable; some complex views cannot be changed directly.

Examples

Creates a view showing only active customers.

MySQL

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

Updates the email of the customer with id 1 through the view.

MySQL

UPDATE active_customers SET email = '[e-mail]' WHERE id = 1;

Adds a new active customer through the view.

MySQL

INSERT INTO active_customers (id, name, email) VALUES (5, 'Anna', '[e-mail]');

Sample Program

This example creates an employees table, inserts data, creates a view for sales department employees, updates salary through the view, and then shows the updated data in the original table.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
(1, 'John', 'Sales', 50000),
(2, 'Jane', 'HR', 55000),
(3, 'Mike', 'Sales', 52000);

CREATE VIEW sales_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

-- Update salary through the view
UPDATE sales_employees SET salary = 53000 WHERE id = 3;

-- Select to see the change
SELECT * FROM employees WHERE id = 3;

Important Notes

Views that use joins, aggregates, or groupings often cannot be updated directly.

MySQL allows updates on simple views that map directly to one table.

If a view is not updatable, you can use triggers or stored procedures to handle changes.

Summary

Updatable views let you change data through a saved query.

They simplify working with complex or sensitive data.

Not all views can be updated; simple views on one table usually can.