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.
Query for this
update empwithdepartment set full_name='flower blossam' where EmpID=2;
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;
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.
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.
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;
Sagar Jaybhay 