Views in Database

Shubham Srivastava

14y1.7k

hi,
What is the difference between updatable and non updatable view?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Hi,

Yes, view can be updated only when it belongs to one table.

If the view has joined with many table then it does not have any inbluilt mechanism to update all the relevent tables. Actually you can't do that.

For example,

we have a table called "Products"

CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) PRIMARY KEY,
[ProductName] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductVendor] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)

I want to insert two records.

INSERT INTO Products(ProductName,ProductVendor) VALUES ('4-Stroke','Hero Honda')
INSERT INTO Products(ProductName,ProductVendor) VALUES ('2-Wheeler','TVS')

Let me create the view on this table.

CREATE VIEW VW_Product_View
AS

SELECT ProductID, ProductName, ProductVendor FROM Products

Now i want to update the products table using the view.

UPDATE VW_Product_View SET ProductName='3-Wheeler', ProductVendor='Ape' WHERE ProductID=2

It is updated! Because it belongs to only one table.

The same thing can't be possible to do with other view.

CREATE VIEW VW_ProdictsSalesView
AS
SELECT PM.ProductID, PM.ProductName,S.CustomerID,S.Price FROM Products PM INNER JOIN Sales S ON PM.ProductID = S.ProductID
Jignesh Trivedi14y ago
hi,
DML (INSERT, UPDATE and DELETE statements) may be used on views.
The operation is then performed on the base table upon which the view is defined.
certain views may not be updated because a view containing DISTINCT values, where a single row in the view may represent several rows in the base table.
this is an example.
other reason for view becomes unuptable are follow
1. the keyword DISTINCT is used in the view definition.
2. the select list contains components other than column specifications, or contains more than one specification of the same column.
3. the FROM clause specifies more than one table reference or refers to a non-updatable view.
4. the GROUP BY clause is used in the view definition.
5. the HAVING clause is used in the view definition .
6. top keyword used in view defination.
7. An aggregate function used in view defination.
hope this help.