View For Updating Data in SQL

Introduction

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 tricky for novice programmers or recent college graduates.

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

But the "dikhawo pe mat jao" tagline suits this SQL object. 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

Summary

This article taught us how to use View For Updating Data in SQL. Find a detailed article about Views in SQL Server.