Updatable View in SQL By Sagar Jaybhay

In this article we will understand how to Update view or What it means In SQL Server By Sagar Jaybhay.

Before Proceeding To this article Please Read Part 1 - https://sagarjaybhay.com/view-in-sql-by-sagar-jaybhay-2020/

Updatable View in SQL Server

By using view it is possible to update the base table.

By using view it is possible to update the base table.

Updatable View 2

Query for this

update empwithdepartment set full_name='flower blossam' where EmpID=2;

Updatable View 3

See the above image you will see the result the data is updated but it not present in view. It is updated in the underlying table so If you want to find this whether it is updated or not fire below query.

select top 4 * from empwithdepartment;

Updatable View 4

In
the above image, you will find the base table also gets updated.

Like the update, you can also delete data from view or insert data into view which turn deleted or inserted in the underlying table.

Updatable View 5

But in this case, we got an error why.

Msg 4405,
Level 16, State 1, Line 35

View or function 'empwithdepartment' is not updatable because the modification affects multiple base tables.

Because
our view is based on multiple tables so we are not able to insert records in
view or it might update records incorrectly but if our view is based on a
single table then we are able to add records in view which in turn added in a
base table.

When you trying to insert data into view and view finds that multiple tables contain these fields so it gets confused and throws an error.

Updatable View 6

In the above figure, you will understand we create a view that is based on a single table and we are able to insert records using the view.

create view simpleview
as
select EmpID,full_name,Salary,Gender from Employee where Gender='Male';

insert into simpleview values(1002,'Sagara',20000,'Male');

select * from simpleview where EmpID>1000;
select * from Employee where EmpID=1002;