Updating a View
Learn how to update and modify data through SQL Server Views, understand the UPDATE statement, and learn the important rules and limitations of updating Views.
What Does Updating a View Mean?
A SQL Server View is a virtual table based on a SELECT query. In many cases, data in the underlying table can be modified through a View.
The UPDATE statement can be used with an updatable View to modify data in the underlying table.
However, not every View is updatable. Views containing certain SQL operations such as GROUP BY, aggregate functions, DISTINCT, UNION, and some complex JOIN operations may not be directly updateable.
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 45000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 55000 |
| 4 | Sneha | Finance | 50000 |
Step 1: Create an Updatable View
First, create a simple View based on the Employees table.
CREATE VIEW EmployeeDetailsView
AS
SELECT
EmployeeId,
EmployeeName,
Department,
Salary
FROM Employees;
This View directly represents columns from the Employees table and can generally be updated.
Step 2: Update Data Through the View
We can use the UPDATE statement to modify a record through the View.
UPDATE EmployeeDetailsView
SET Salary = 50000
WHERE EmployeeId = 1;
The salary of employee with EmployeeId 1 is updated. Because the View is based on the Employees table, the underlying Employees table is also modified.
Step 3: Verify the Updated Data
We can use SELECT to verify the updated record.
SELECT *
FROM EmployeeDetailsView
WHERE EmployeeId = 1;
The updated salary will now be displayed in the result.
Updating Multiple Columns
Multiple columns can be updated at the same time through an updatable View.
UPDATE EmployeeDetailsView
SET
Department = 'Development',
Salary = 60000
WHERE EmployeeId = 3;
This statement changes both the department and salary of the employee.
Updating Data Using a Condition
The WHERE clause should normally be used when updating specific records.
UPDATE EmployeeDetailsView
SET Salary = Salary + 5000
WHERE Department = 'IT';
This increases the salary of employees belonging to the IT department by 5000.
Updating Through a Filtered View
Consider a View that contains only IT employees:
CREATE VIEW ITEmployeesView
AS
SELECT
EmployeeId,
EmployeeName,
Department,
Salary
FROM Employees
WHERE Department = 'IT';
We can update the salary through this View:
UPDATE ITEmployeesView
SET Salary = 65000
WHERE EmployeeId = 3;
The underlying Employees table will be updated.
Checking the Underlying Table
After updating through a View, we can check the original table to verify that the data has changed.
SELECT *
FROM Employees
WHERE EmployeeId = 3;
The updated value will be visible in the Employees table.
When Can a View Be Updated?
A simple View is generally updateable when the View directly references columns from a single underlying table and does not contain operations that prevent direct modification.
Example of a Simple Updatable View
CREATE VIEW SimpleEmployeeView
AS
SELECT
EmployeeId,
EmployeeName,
Salary
FROM Employees;
This type of View can generally be used for updating the underlying data.
Views That May Not Be Directly Updateable
Some Views are not directly updateable because they contain operations that change or summarize the result set.
| SQL Feature | Why It Can Prevent Direct Updates |
|---|---|
| GROUP BY | Groups multiple rows into summarized results. |
| Aggregate Functions | Functions such as SUM(), AVG(), COUNT(), MIN() and MAX() produce calculated results. |
| DISTINCT | Removes duplicate rows from the result. |
| UNION | Combines results from multiple queries. |
| Complex JOINs | Some complex multi-table Views cannot be directly updated. |
Important Warning
Always use a proper WHERE condition when updating data. If the WHERE clause is omitted, all rows affected by the View may be updated.
Best Practices
- Always test the SELECT query before performing UPDATE.
- Use a WHERE clause to target specific records.
- Understand whether the View is updateable before attempting an UPDATE.
- Avoid updating large numbers of records accidentally.
- Take appropriate backups before performing important database modifications.
Summary
An updatable SQL Server View can be used to modify data in its underlying table. The UPDATE statement can be executed against a suitable View just like a table.
However, Views containing operations such as GROUP BY, aggregate functions, DISTINCT, UNION, or certain complex JOINs may not be directly updateable. Always use a proper WHERE clause when modifying data.