Use View to update Information in Underlying Tables

In "SQL Server 2022", you can create a view and, if it meets certain conditions, use an `UPDATE` statement to modify values through the view. Below is a full example:

1. ✅ Step 1: Create the Base Table 
   

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName NVARCHAR(50),
       LastName NVARCHAR(50),
       Department NVARCHAR(50),
       Salary DECIMAL(10, 2)
   );
   

2. ✅ Step 2: Insert Sample Data 
   

   INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
   VALUES 
   (1, 'Alice', 'Johnson', 'HR', 60000.00),
   (2, 'Bob', 'Smith', 'IT', 75000.00),
   (3, 'Carol', 'Lee', 'IT', 80000.00);
   

   
   
3. ✅ Step 3: Create a View 
   This view selects only IT employees.

   CREATE VIEW IT_Employees AS
   SELECT EmployeeID, FirstName, LastName, Salary
   FROM Employees
   WHERE Department = 'IT';
   

   
   ✅ Since this view:
    
   • is based on a single table,
    
   • does not include aggregates, joins, GROUP BY, or DISTINCT,
    
   • and includes the primary key (`EmployeeID`),
    
   • You can update data through this view.
4. ✅ Step 4: UPDATE the View 
   This example increases salary by 10% for employee with `EmployeeID = 2`.
   

   UPDATE IT_Employees
   SET Salary = Salary * 1.10
   WHERE EmployeeID = 2;
   

5. ✅ Step 5: Verify the Update 
   

   SELECT * FROM Employees WHERE EmployeeID = 2;
   

   
   You should see Bob’s salary updated from `75000.00` to `82500.00`.

⚠️ Important Notes

• You cannot update a view that contains:
  • Aggregates (`SUM`, `AVG`, etc.)
  • `DISTINCT`
  • Joins (unless using `INSTEAD OF` triggers)
  • `GROUP BY`, `HAVING`, or set operations like `UNION`
• To work around limitations, use `INSTEAD OF UPDATE` triggers.

With some engines, you can even protect the SELECT statement associated with your view. This lets you set up the view, but at the same time make sure that people cannot use your view to figure out the underlying tables. If you are working with confidential information, you will want to look into encrypting your views to protect them. Views can be updated only if all elements of the view are a subset of the base table.
Suppose there exists a base table

empid name and addr columns

then a view on this table is created as

create view myview as 
select * from emp;

only these type of views are updatable.

create view myview as select name addr from emp; 

is not updatable.

Suppose you have the following table named PhoneNumbers, in which CustomerID is a unique identifier:
PhoneNumbers Table with primary key CustomerID

CustomerID

PhoneNumber

Also suppose you have the following view defined:

CREATE VIEW MyView 
AS SELECT  PhoneNumber 
FROM PhoneNumbers

This view would be read-only because it is not possible to understand which row to update. You've left out the CustomerID, which is the key to the table. If, however, you add the key (CustomerID) to the view definition, you will be able to update it because each row would have only one possible occurrence based on this ID. Both of these will be updateable:

CREATE VIEW
MyView AS
SELECT
CustomerID, PhoneNumber from PhoneNumbers

or

CREATE VIEW
MyView AS
SELECT * FROM PhoneNumbers

Try this exercise to create a view and then use it to update a table.
Update View - Exercise