Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

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.