How to create an Updateable View in SQL Server

A View allows a user to see certain records of a table or tables. It can provide protection from accidental editing of other fields or records in the table. Views are Updateable even when they reference different tables but when you update, they must only update one table in Sql Server. To see an illustration of what I mean, create the following tables. Its a very basic example but hopefully, you'll see what i mean.

Create table Emp
(
    Id int identity(1,1),
     Name varchar(20),
     Department varchar(200),
    Office varchar(20)
)
    Insert into Emp values ('John Walker','Accounts','Birmingham')
    Insert into Emp values ('Steve Jones','Resources','Derby')
    Insert into Emp Values ('Sarah Carter','Accounts','Birmingham')
    Insert into Emp Values ('Alexa Bond','Accounts','Birmingham')
    Insert into Emp Values ('Lewis Jackson','Resources','Derby')
    Insert into Emp Values ('Louise Scott','Resources','Derby')
go

Create table Office (id Int identity(1,1), OfficeName varchar(20), County varchar(20),Country Varchar(20))
    Insert into Office values ('Derby','Derbyshire','England')
    Insert into Office values ('Birmingham','West Midlands','England')
go

Now we create the view, you can either you the editor that comes built in with the Management Studio or create the view using a text editor. To create a view using the SMSS, drill down the object explorer to Database -> Your Database -> Views and then right click to create view. For our example, we'll use SQL.

Create View EmployeeCounty as

    Select E.Name, E.Department, E.Office as Office, O.County
        from Emp E left join Office O on E.office = O.Office

go

If you are creating an editable view, there are a few things you need to be aware of. Views will allow them but when you attempt to amend data, an error will be thrown.

• All fields must be pure fields and not derived, that is using :-
  • AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP
• Cannot be created using any of the following computational fields.
  • UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT
• The following commands may not be used in the SQL.
• GROUP BY, HAVING, or DISTINCT
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
• All fields being updated can only reference one field in the View.

The following SQL examples are allowed.

Update EmployeeCounty Set Name = 'Alexa Porter' where name = 'Alexa Bond'

Both tables that make up the view have fields called Office but as only the Emp Office is selected then only that table will be amended.

Update EmployeeCounty set Office = 'Leicester' where office = 'Derby'

Just to show that Office can be updated, here is an example..

Update EmployeeCounty Set County = 'Birminghamshire' where County = 'West Midlands'

The following cannot be updated and will cause an error if you try to.

Update EmployeeCounty Set Name = 'John Lord', County = 'Birmingham' where Name = 'John Walker'

Country is not part of the view and will remain forever England. Any attempt at updating England will result in an error.

Update EmployeeCounty set Country = 'France' where Country = 'England'

Tags - SQL

Last Modified : June 2023