View For Updating Data in SQL

I encountered an interview recently that included a question asking how to insert and delete a process on the view.

This question has always proven very tricky for novice programmers or just recent college graduates.

Most people attempt to guess by saying that if the object name is view then it might be used to view the data. And we also use mostly view as showing data.

But the "dikhawo pe mat jao" tag line is suitable for this SQL object. Definitely, you can insert and update from the view.

Let's go for an exercise

Run the following scripts to create Student and Teacher tables.

Create Table Teacher
(
      iid Integer Identity(1,1)
      ,TeacherName varchar(255)
      Constraint pk_TeacherIID Primary key(iid)
); 

SET IDENTITY_INSERT [dbo].[Teacher] ON
INSERT [dbo].[Teacher] ([iid], [TeacherName]) VALUES (1, N'Mr. Kailash')
INSERT [dbo].[Teacher] ([iid], [TeacherName]) VALUES (2, N'Mr. Dharmesh')
INSERT [dbo].[Teacher] ([iid], [TeacherName]) VALUES (3, N'Miss Naina')
SET IDENTITY_INSERT [dbo].[Teacher] OFF
 
Create Table Student
(
      iid Integer Identity (1,1)
      ,Name varchar(255) NOT NULL
      ,ClassTeacherID int NOT NULL
      ,Std TinyInt NOT NULL  
      Constraint pk_StudentIDD Primary Key  (iid),
      Constraint fk_Student_Teacher foreign key (ClassTeacherID) references Teacher(iid)
); 
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([iid], [Name], [ClassTeacherID], [Std]) VALUES (1, N'Kamlesh Shah', 1, 4)
INSERT [dbo].[Student] ([iid], [Name], [ClassTeacherID], [Std]) VALUES (2, N'Dhiraj Mehta', 1, 4)
INSERT [dbo].[Student] ([iid], [Name], [ClassTeacherID], [Std]) VALUES (3, N'Richa Patel', 1, 4)
SET IDENTITY_INSERT [dbo].[Student] OFF 
Create View vwStudentInfo
AS
select
      s.iid AS [RollNo]
      ,s.Name
      ,t.TeacherName
      from Student s
      Inner join Teacher t
      On s.ClassTeacherID = t.iid; 
select * from vwStudentInfo



Now, the following query changes the data using the view.

Alter the data

Update vwStudentInfo
      set Name = 'Dhanjay Mehta'
      Where RollNo = 2
select * from Student