Using SQL Views to Insert/Update/Delete Data

In previous post we discussed SQL Server Views and how you can use them to logically abstract some of the complexities of the underlying schema. We focused mainly on selecting data using views.

Views also allow you to insert/update/delete data, but they are limited in their power when it comes to modifying data.

Think back for a second - when you insert data in a table, you can only insert in one table at a time. If more than one table is related via referential integrity, you must insert in one table and then insert in subsequent tables using the referential integrity key from the primary table. Similarly, when you are updating or deleting data from the tables, you can only update/delete from one table at a time. Additionally, the user must have proper permissions to do.

Same restrictions apply when you are using views to achieve this. Some of the restrictions that apply to views are...

• Insert/Update/Delete statements must reference columns from only one base table.
• Columns in the view must relate to underlying columns in the base table directly i.e. they cannot be computed columns such as AVG, COUNT, SUM, MIN, MAX etc. 
• The columns being modified cannot also be grouped or affected by DISTINCT, GROUP BY or HAVING clauses
• If you think about it, it makes sense. SQL Server wouldn't know which column to modify if you have applied these clauses.
• If you have used WITH CHECK OPTION (see previous post about this), you can't use TOP in SELECT statement of the view.
• If you have any sub/nested queries, same restrictions apply to them as well.
• Constraints defined at the table columns such as not null, referential integrityetc. also apply when modifying via views.

In previous post we created a view to retrieve some records from multiple tables. Let's see if we can use the same view to update or delete a record.

CREATE VIEW view_Orders

AS
SELECT C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers 
C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName
GO

Since both customer's and sales person's first and last name are used in Group By clause and also the OrderID column is using Count function (OrderID is identity column so you can't update anyway, but just wanted to make a point), none of the columns in this view can be updated or deleted.

What if I create a view like this...

CREATE VIEW view_OrdersList

AS
SELECT C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
O.OrderID As OrderNumber,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers 
C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
GO

I should be able to use this view to pretty much update any column i.e. Customer First Name, Last Name and Sales Person First and Last Name. I can't update Order ID because it is identity column.

In my database, this view returns the following records

I can run the following update commands one after another to update the customer last name and sales person last name using the same view, even though they are in two different underlying tables.

UPDATE View_OrdersList SET CustomerLastName='Roberts' WHERE 
CustomerLastName='Smith'
GO
UPDATE View_OrdersList SET SalesLastName='Kimberly' WHERE 
SalesLastName='Kimber'
GO

But what about updating both records using the same update query?

UPDATE View_OrdersList SET CustomerLastName='Roberts',SalesLastName='Kimberly' 
WHERE CustomerLastName='Smith' AND SalesLastName='Kimber'
GO

This will result in the following error

Needless to say it violates the above defined rules i.e. only one table can be updated at a time, hence the error.

I can also insert using the same view or delete a record as long as no referential integrity or other constraints are violated.

A view abstracts away some of the complexities and allows you to use the same view to select/update/insert/delete, although you still have to ensure all the conditions are satisfied just as you would when making modifications directly in tables.

Thank you.