Views do not maintain a separate copy of the data, but only display the data present in the base tables. Therefore, you can modify the base tables by modifying the data in the view, however, the following restrictions exist while inserting, updating, or deleting data through views:
For example, a view displaying the employee id, manger id, and rate of the employees has been defined using the following statement:
CREATE VIEW vwSal AS
SELECT i.EmployeeID, i.MangerID, j.Rate FROM HumanResources.Employee AS i
JOIN HumanResources.EmployeePayHistory AS j ON
i.EmployeeID = j.EmployeeID
After creating the view, if you try executing the following update statement, it generates an error. This is because the data is being modified in two tables through a single update statement.
UPDATE vwSal
SET ManagerID = 2, Rate = 12.45
WHERE EmployeeID = 1
Therefore, instead of a single UPDATE statement, you need to execute two UPDATE statement for each table.
The following statement would update the EmployeeID attribute in the Employee base table:
UPDATE vwSal
SET ManagerID = 2
WHERE EmployeeID = 1
The following statement would update the Rate attribute in the EmployeePayHistory table:
UPDATE vwSal
SET Rate = 12.45
WHERE EmployeeID = 1
Therefore, to modify the data in two or more underlying tables through a view, you need to execute separate UPDATE statements for each table.
- You cannot modify data in a view if the modification affects more than one underlying table. However, you can modify data in a view if the modification affects only one table at a time.
- You cannot change a column that is the result of a calculation, such as a computed column or an aggregate function.
For example, a view displaying the employee id, manger id, and rate of the employees has been defined using the following statement:
CREATE VIEW vwSal AS
SELECT i.EmployeeID, i.MangerID, j.Rate FROM HumanResources.Employee AS i
JOIN HumanResources.EmployeePayHistory AS j ON
i.EmployeeID = j.EmployeeID
After creating the view, if you try executing the following update statement, it generates an error. This is because the data is being modified in two tables through a single update statement.
UPDATE vwSal
SET ManagerID = 2, Rate = 12.45
WHERE EmployeeID = 1
Therefore, instead of a single UPDATE statement, you need to execute two UPDATE statement for each table.
The following statement would update the EmployeeID attribute in the Employee base table:
UPDATE vwSal
SET ManagerID = 2
WHERE EmployeeID = 1
The following statement would update the Rate attribute in the EmployeePayHistory table:
UPDATE vwSal
SET Rate = 12.45
WHERE EmployeeID = 1
Therefore, to modify the data in two or more underlying tables through a view, you need to execute separate UPDATE statements for each table.