Can we write Insert,Update and Delete Queries In Views?

In the post, we learned about updatable views. Before continuing with this session, I strongly recommend reading the post below first.

• What is a view and the advantages of views with an example

what are updatable views?

Let's illustrate that with an example. Consider a table named TblProfessor, which contains columns for Id, name, salary, gender, and University_Id.

Typically, salary information is considered confidential, and it's not ideal for employees to have access to other employees' salary details. However, there might be scenarios where a specific user requires access to employee details.

In such cases, granting direct access to the table would allow the user to see all rows and columns, including salary information, which we want to restrict. To address this, we can create a view that selects all columns except the salary column, thereby concealing sensitive salary details.

Create View vWTblProfessor_withoutsalary_Detail
as

Select Id, Name, Gender
from TblProfessor

Indeed, in the select statement for creating the view, all columns are selected except for the salary column. When you query data from this view, you'll observe that the result contains all columns except for the salary column, fulfilling the requirement to conceal sensitive salary information.

SELECT * from vWTblProfessor_withoutsalary_Detail

Now, if you examine the view itself, does it actually store any data? No, a view is simply a stored SELECT statement. It's a virtual table, not a physical one.

So, when you execute the query, the view retrieves its data from the TblProfessor table. In this case, TblProfessor serves as the underlying base table for the view.

When you issue a SELECT * FROM view_name, the view fetches its data directly from the underlying base table because the view itself doesn't store any data.

Since you can execute statements against a view, which then retrieves data from the underlying base table, can you update a view to modify the underlying base table? Absolutely. That's entirely possible.

You can update a view, insert data into it, and delete data from it. These operations are reflected in the underlying base table, in this case, TblProfessor. So, when you update or insert into this view, it effectively updates the underlying base table.

How to create updatable view in sql server?

Now let’s update the view. So I’m updating the view update view and we are saying the set name is equal to ‘Mark ‘where Id = 1.

Update vWTblProfessor_withoutsalary_Detail Set Name = 'Mark' Where Id = 1

Let’s execute the above query and then select data from the view

Now let’s select the data from The View. as you can in the above image ,name have been changed to ‘Mark‘.

Can we update real data in views?

Now, let's select data from the TblProfessor table and verify if the underlying base table has been updated. Upon inspection of the underlying base table TblProfessor, you'll notice that the name has indeed been updated to 'Mark'.

Hence, it's confirmed that it's possible to update the base table using views. In this scenario, we refer to the view as an updatable view, and in SQL Server, our views are indeed updatable.

Delete and insert data from a view

Now, we can also delete and insert data from a view, which in turn will obviously delete and insert data from the underlying table. Let's attempt to delete a record with an ID equal to 2, in this case, the record for Mackenzie.

Delete from vWTblProfessor_withoutsalary_Detail where Id = 2

So let’s exclude and then select the data back from view and see if it’s really deleted.

As you can see in the above image ,Id=2 is deleted from the table. We can also insert data into the view.

SQL Updatable View with joined tables

Take a look at the view I'm creating here. I'm using both the TblProfessor and TblUniversity tables. This view returns the Id, name, salary, gender, and University name.

The first four columns (Id, name, salary, and gender) are sourced from the TblProfessor table, while the University name is retrieved from the TblUniversity table.

Create View vWTblProfessorDetail
as

Select Id, Name, Salary, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblProfessor.University_Id = TblUniversity.University_Id

We're creating a view that joins the TblProfessor table with the TblUniversity table. When you have a view based on multiple tables and update it, what happens? That's what we'll be examining next.

Now, if you look at Connor's record with Id=4, Connor is currently associated with Arizona State University.

Before we proceed to update his University to McGill University from Arizona State University, let's take note of who else is associated with Arizona State University along with Connor.

Chase is also affiliated with Arizona State University. Currently, there are two professors, Connor and Chase, within Arizona State University.

When I executed the following update statement, we updated Connor's university to be McGill University from Arizona State University. Therefore, only Connor's university should be changed to McGill University, while Chase should remain associated with Arizona State University.

Update vWTblProfessorDetail
set UniversityName='McGill University' where Name = 'Connor'

So let’s update this and see what’s going to happen and then let’s select the data back from The View.

Connor's university has been correctly updated to 'McGill University', which is good. However, let's examine Chase's university. We expected it to remain 'Arizona State University', but it appears to have been changed to 'McGill University'. This discrepancy indicates that our update statement didn't execute as expected. Let's analyze why this happened.

Whether your view is based on a single base table or multiple base tables, when you update the view, it will update the underlying base tables. This is because the view itself doesn't store any data. By default, this behavior is in place, although it can be altered using materialized views. 

When you issue select, insert, update, or delete statements, they are executed against the underlying base tables. So in this case, when we updated the view, it actually updated the underlying base tables, TblProfessor and TblUniversity.

Let's select data from those tables and see what's actually happening.

If you look at this, if you look at the TblUniversity’s data and look at this, Connor’s University_Id is 3. Chase’s University_Id is 3.

When you issued the update statement, you specified that for records where the name is 'Connor', the University name should be set to 'McGill University'. This implies that the University_Id for Connor's record should be changed from 3 to 7.

However, what actually occurred was that when you updated the view, it incorrectly updated the underlying base table. It changed 'Arizona State University' to 'McGill University' in the TblUniversity table.

So, if your view is based on multiple tables and you intend to update the view, it may not update the underlying base tables correctly.

To ensure proper updating of views based on multiple tables, you can use INSTEAD OF triggers.

Table Create Sql Script

/****** Object: Table [dbo].[TblProfessor] Script Date: 09/30/2020 5:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProfessor](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Salary] [float] NULL,
[University_Id] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblUniversity] Script Date: 09/30/2020 5:35:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
[University_Id] [int] IDENTITY(1,1) NOT NULL,
[UniversityName] [nvarchar](max) NULL,
CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED
(
[University_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] ON
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (1, 'Christopher', 'Male', 50000, 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (2, 'Mackenzie', 'Female', 60000, 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (3, 'Julia', 'Female', 40000, 4)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (4, 'Connor', 'Male', 30000, 3)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (5, 'Madeline', 'Female', 90000, 6)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (6, 'Easton', 'Male', 35000, 5)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (7, 'Gianna', 'Female', 40000, 7)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (8, 'Chase', 'Male', 32000, 3)
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Carnegie Mellon University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Michigan State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Arizona State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'The Australian National University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'The University of Arizona')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'The University of Melbourne')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'McGill University')
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblProfessor] WITH CHECK ADD CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblProfessor] CHECK CONSTRAINT [FK_TblStudent_University]
GO