View is a virtual table, which can contains data (rows with columns) from one or more table and you can retrieve data from a view.
Let’s demonstrate a simple example in which we will see that how to create a view on single table and will also see that if we update the view the respective table on which the view is created is updated or not.
Now let first see how to create view.
Go to View folder in SQL Server > Right click on it > select New View.
After selecting the column name just save the view and give View a nice name.
Once you have completed the above step you will see that the respective View is added in the View folder.
Now let’s see that when we update the view the respective table is also updated or not.
Update [Practice].[dbo].[Cust_View] set Customer_Contact = 96641122 where Customer_Name = 'Feroz'
Now just go to the table on which the view was created and check whether the table is updated or not, you will see that the table is also updated when you update the View.
Now let’s create a view based on two tables and try to update a view.
create view View_Cust as SELECT dbo.Customer.Customer_Name, dbo.Customer.Customer_Contact,dbo.[Order].Product_Name,dbo.[Order].Price FROM dbo.Customer INNER JOIN dbo.[Order] ON dbo.Customer.Order_ID = dbo.[Order].Order_IDLet’s try to Update View:
Update [Practice].[dbo].[View_Cust] set Customer_Contact = 098767554, Price = 4000 where Customer_Name = 'Feroz'As you can see in the above query, I am trying to update a column from the Product table and another column from the Order table and try to execute the query the compiler will throw the below error.
Error Message:- View or function 'Practice.dbo.View_Cust' is not updatable because the modification affects multiple base tables.
This means that when you try to update both the table’s column from the view then it is not allowed but you can update single table column.
write a comment