SQL Notes
Learn how to update views in SQL using CREATE OR REPLACE VIEW, ALTER VIEW, and understand which views are updatable for INSERT, UPDATE, and DELETE operations.
Views are virtual tables defined by a query. But what happens when your requirements change? Maybe you need to add a column, change the filtering logic, or modify the underlying query. And what about modifying data through a view — can you INSERT, UPDATE, or DELETE rows through a view?
This lesson covers two related topics: modifying the view definition itself, and performing data modifications through a view on the underlying tables.
These are distinct concepts that beginners often confuse. Modifying a view definition means changing the SELECT query that defines the view — adding columns, changing filters, or altering joins. Performing modifications through a view means using INSERT, UPDATE, or DELETE on a view name, which actually changes data in the underlying base tables. Both are important skills, but they serve completely different purposes and have different rules governing when they can be used.
Updatable Views
An updatable view allows you to perform INSERT, UPDATE, and DELETE operations on the view, and the changes are applied to the underlying base table. Not all views are updatable — certain conditions must be met.
Conditions for an Updatable View
A view is generally updatable if:
- It selects from a single table (no joins)
- It does not use DISTINCT, GROUP BY, or HAVING
- It does not use aggregate functions (SUM, COUNT, AVG, etc.)
- It does not use UNION or subqueries in the SELECT list
- Every column in the base table that is NOT NULL or lacks a DEFAULT must be included
Example: Updatable View
CREATE VIEW EngineeringTeam AS
SELECT EmpID, Name, Email, Salary, JoinDate
FROM Employees
WHERE Department = 'Engineering';This view is updatable because it queries a single table with a simple WHERE condition.
UPDATE Through a View
-- This modifies the underlying Employees table
UPDATE EngineeringTeam
SET Salary = Salary * 1.10
WHERE EmpID = 101;The database translates this into:
UPDATE Employees SET Salary = Salary * 1.10 WHERE EmpID = 101;Only employees visible through the view (Department = 'Engineering') should be modified this way, though the database applies the update based on the WHERE clause you provide.
INSERT Through a View
-- Insert a new employee through the view
INSERT INTO EngineeringTeam (EmpID, Name, Email, Salary, JoinDate)
VALUES (150, 'Vikram Joshi', 'vikram@company.com', 75000, '2026-06-14');Important consideration: this insert will succeed, but what value does Department get? Since the view does not include the Department column, the database uses its DEFAULT value. If Department has no default and is NOT NULL, the insert fails.
DELETE Through a View
-- Delete an employee through the view
DELETE FROM EngineeringTeam WHERE EmpID = 150;This deletes the corresponding row from the Employees table.
WITH CHECK OPTION
Here is a tricky scenario: what if you insert or update a row through a view, and the new values make the row invisible to that view?
-- View shows only Engineering employees
CREATE VIEW EngineeringTeam AS
SELECT EmpID, Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering';
-- Update changes department — row disappears from view!
UPDATE EngineeringTeam SET Department = 'Marketing' WHERE EmpID = 101;The employee moves to Marketing and vanishes from the EngineeringTeam view. This is confusing. WITH CHECK OPTION prevents it:
CREATE VIEW EngineeringTeam AS
SELECT EmpID, Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering'
WITH CHECK OPTION;Now:
-- This FAILS: would make the row disappear from the view
UPDATE EngineeringTeam SET Department = 'Marketing' WHERE EmpID = 101;
-- ERROR: CHECK OPTION failed
-- This succeeds: row remains visible in the view
UPDATE EngineeringTeam SET Salary = 80000 WHERE EmpID = 101;Real-World Example: Customer Portal View
-- View for the customer-facing application (limited columns, no internal data)
CREATE OR REPLACE VIEW CustomerOrders AS
SELECT
o.OrderID,
o.OrderDate,
o.Status,
o.TotalAmount,
p.ProductName,
oi.Quantity
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE o.Status != 'Cancelled';This view is NOT updatable because it involves multiple joins. It is read-only — perfect for displaying data to customers without risk of modification.
Real-World Example: HR Salary View
-- Updatable view for HR to manage salaries
CREATE VIEW HRSalaryManagement AS
SELECT EmpID, Name, Department, Salary, Bonus
FROM Employees
WHERE Status = 'Active'
WITH CHECK OPTION;-- HR can update salaries through the view
UPDATE HRSalaryManagement SET Salary = 85000 WHERE EmpID = 105;
UPDATE HRSalaryManagement SET Bonus = 10000 WHERE Department = 'Sales';Checking if a View is Updatable
MySQL
SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'your_database';SQL Server
You can check by attempting a modification — SQL Server will raise an error if the view is not updatable.
Non-Updatable View Examples
These views cannot be used for INSERT, UPDATE, or DELETE:
-- Uses JOIN — not updatable
CREATE VIEW OrderSummary AS
SELECT c.Name, COUNT(o.OrderID) AS OrderCount
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name;
-- Uses aggregate functions — not updatable
CREATE VIEW DepartmentStats AS
SELECT Department, AVG(Salary) AS AvgSalary, COUNT(*) AS EmpCount
FROM Employees GROUP BY Department;
-- Uses DISTINCT — not updatable
CREATE VIEW UniqueCategories AS
SELECT DISTINCT Category FROM Products;Common Mistakes to Avoid
Mistake 1: Assuming all views are updatable. Views with JOINs, GROUP BY, DISTINCT, or aggregates are read-only.
Mistake 2: Forgetting WITH CHECK OPTION. Without it, modifications can make rows disappear from the view, causing confusion.
Mistake 3: Inserting through views that exclude NOT NULL columns. If the base table has required columns not in the view, INSERTs will fail.
Mistake 4: Using DROP VIEW when CREATE OR REPLACE works. DROP loses all permissions. CREATE OR REPLACE preserves them.
Best Practices
- Use CREATE OR REPLACE to modify views without losing permissions
- Add WITH CHECK OPTION on views used for data entry to prevent invisible rows
- Keep updatable views simple — single table, no aggregates
- Use non-updatable views for reporting — JOINs and aggregates are fine for read-only views
- Document which views are updatable so developers know what operations are safe
- Test modifications on a development database before applying to production
Understanding view modifications is essential for building secure, maintainable database applications. Updatable views provide controlled data access, while CREATE OR REPLACE keeps your view definitions current as requirements evolve.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Update and Modify Views in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, views, update, view
Related SQL Complete Guide Topics