Updatable Views in SQL Server

If you provide users with a set of views to use for data retrieval, what should you do if the users also need to modify the same data? It might seem a bit strange, for example, to force the users to retrieve data using a view, but then allow them to directly modify the underlying table using update or insert statements. For this purpose, MySQL, Oracle Database, and SQL Server all allow you to modify data through a view, as long as you abide by certain restrictions. A view is updatable if the following conditions are met:

No aggregate functions are used (max(), min(), avg(), etc.). The view does not employ group by or having clauses.
No sub queries exist in the select or from clause, and any subqueries in the where clause do not refer to tables in the from clause.
The view does not utilize union, union all, or distinct.

The from clause includes at least one table or updatable view.
The from clause uses only inner joins if there is more than one table or view.

To demonstrate the utility of updatable views, it might be best to start with a simple view

definition and then to move to a more complex view.

 

For Example:

You are creating the view:

Create View as UpdateExample

As
Select * from
Employee inner E join Employee_Location EL on E.EID=EL.EID
 

Then if you want to update employee location and Employee name in one go you can write update statement for view which in turns can update underlying table.

Update UpdatExample

Set Loc=’X’,
     Lastname=’nam’
Where EID=123