SQL - Update Views

SQL - Updating Data Through a View

The SQL UPDATE Statement on a View

SQL does not have a specific ALTER VIEW or UPDATE VIEW command to change the data it displays. Instead, you use the standard UPDATE statement. When you update a view, you are actually modifying the data in the underlying base table(s). This is only possible if the view is “updatable”.

Important: An UPDATE on a view modifies the source table’s data. Views are windows into data, not copies of it.

Syntax

The syntax is identical to updating a table:

UPDATE view_name
SET column1 = value1, column2 = value2, ...
WHERE [condition];

The Concept of Updatable Views

For an UPDATE operation on a view to succeed, the database must be able to trace the changes back to a specific row and column in a single base table. A view is generally NOT updatable if it includes:

• Joins on multiple tables
• Aggregate functions (SUM, AVG, etc.) or GROUP BY / HAVING clauses
• DISTINCT
• Window Functions
• Set operators (UNION, etc.)
• Calculated columns that you are trying to update

Example: Updating a Simple View

Let’s create a table and a simple, updatable view of customers in a specific city.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    city VARCHAR(100),
    last_contacted_on DATE
);

INSERT INTO customers VALUES
(1, 'John Doe', 'john.d@example.com', 'New York', '2023-01-10'),
(2, 'Jane Smith', 'jane.s@example.com', 'London', '2023-02-15'),
(3, 'Peter Jones', 'peter.j@example.com', 'New York', '2023-03-20');

-- Create a view for customers in New York
CREATE VIEW v_ny_customers AS
SELECT customer_id, name, email, last_contacted_on
FROM customers
WHERE city = 'New York';

This view is updatable because it references a single table and doesn’t contain any complex operations. Let’s update Peter Jones’s email through the view.

UPDATE v_ny_customers
SET email = 'p.jones.new@example.com'
WHERE customer_id = 3;

Verification

The change is reflected in both the view and the underlying customers table.

-- Query the base table to confirm the change
SELECT * FROM customers WHERE customer_id = 3;

customer_id	name	email	city	last_contacted_on
3	Peter Jones	p.jones.new@example.com	New York	2023-03-20

Example: Attempting to Update a Non-Updatable View

Now, let’s create a view with a calculated column. Such a view is not updatable.

CREATE VIEW v_customer_summary AS
SELECT
    customer_id,
    name,
    UPPER(city) AS city_uppercase
FROM customers;

-- This will FAIL because `city_uppercase` is a calculated column.
UPDATE v_customer_summary
SET city_uppercase = 'LONDON'
WHERE customer_id = 2;

The database will return an error because it cannot reverse the UPPER() function to update the original city column in the customers table.

Updating Multiple Rows and Columns

You can update multiple columns at once and affect multiple rows, provided the view is updatable. Be extremely careful with the WHERE clause.

Critical Warning: Omitting the WHERE clause in an UPDATE statement will modify all rows in the underlying table that are visible through the view. Always double-check your WHERE clause before execution.

Example

Let’s update the contact date for all customers in our New York view.

-- Update the last contacted date for all customers in the view
UPDATE v_ny_customers
SET last_contacted_on = '2024-01-01';

This query will change the last_contacted_on date for both John Doe and Peter Jones in the customers table, as they are the only records that satisfy the view’s criteria.