- Depending on the complexity of the view, you may not be able to modify the data through the view to the underlying base tables.
Modifying tables through viewsIn some cases, you can update the tables through a SQL view depending on its complexity.
You can only update views with a single base table otherwise it may choose the incorrect base table to update.
INSERT INTO vwProductsNorthwind VALUES (@ItemName, @ItemCost);Figure: Example of an updatable view using a single base table
UPDATE vwProductsNorthwind SET Cost = @ItemCost WHERE Id = @ItemId;
DELETE vwProductsNorthwind WHERE Id = @ItemId;
More complex views, such as a multi-table view can be used after the where clause in another update statement.
-- Create the products by category viewFigure: Using a multi-table view after the when clause
CREATE VIEW vwProductsByCategory
SELECT p.Id, p.Name, p.Cost, p.OnSale, p.CategoryId
FROM Products p
JOIN Categories c
ON p.CategoryId = c.Id
-- Set all products from a particular category to be on sale
SET OnSale = @OnSale
WHERE Id IN ( SELECT Id FROM vwProductsByCategory WHERE CategoryName = @CategoryName )
So your business has an employees table as shown below that has detailed information about their name, birthdate, home phone, address and photo. This information is suitable for the payroll department but what you want to display employees names and photos on the website for public viewing. Or what If you want contact information such as extension number and country to be available on the company intranet?
Figure: Northwind traders employees table
You could create separate tables for each department, only supplying the required fields for each.
This would also need an additional system to sync between the tables to ensure the information was kept up to date.
Figure: Bad Example – Using tables and duplicating data
CREATE VIEW vwIntranetEmployees AS Figure: Good Example – Using views from the base table containing the source data
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, Country, Extension, Photo, PhotoPath
CREATE VIEW vwWebsiteProfiles AS
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, Photo, PhotoPath
Creating views of the employee table allows you to update the data in one source location such as payroll and all other departments will see the changes. It prevents the problem of stale data and allows more control over access to the data.