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.
By using view it is possible to update the base table.

Updatable View 2
Updatable View 2

Query for this

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

Updatable View 3
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
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
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
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;

View in SQL By Sagar Jaybhay 2020

In this article Sagar Jaybhay explain what is View. What is the use of View. what is the advantages of views in SQL server.

View

The view is saved SQL query or we can call it a virtual table.

View In SQL 1
View In SQL 1

We have these 2 tables and by joining these 2 tables we want to output.

View In SQL 2
View In SQL 2

By joining these 2 tables we get the above result. Now we want to create a view, like other create statement like create a table, create procedure we have to create view statement

Below is a query for creating a view

create view empwithdepartment
as
select e.EmpID,e.full_name,e.Salary,e.Gender,d.Department_Name from Employee as e
join Department as d on d.DepartmentID=e.DepartmentID;

View In SQL 3
View In SQL 3

To find a view in the database refer below image

How to find view
How to find view

If you check the above query it just selects query and if you want to get data from the view you will able to treat it as a table and simple select * from view_name; by using this you can get data.

View doesn’t store any data and it is just saved select query.

Advantages of views

  1. It is used to reduce the complexity of database schema
  2. It provides a mechanism to implement column level and row-level security means if you want to give access to certain users with a limited number of rows and columns then put your query in view and given that view name to end-user so that he performs the operation on that considering this is a table. By doing this end-user doesn’t know the underlying base table.
  3. It Is used to present aggregated data or detailed data.

To alter view you can use alter view syntax

Alter view view_name
As
-	your query syntax here

To drop the view you can use

Drop view view_name;


GitHub : https://github.com/Sagar-Jaybhay