Can we write update and delete queries in views?

In SQL Server 2005, I have some views created by using a SELECT statement. Can we write UPDATE and DELETE statements in views?

7 Answers

from this MSDN article: Modifying Data Through a View,

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:

  • An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
  • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.

• The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.

and see the article for remaining ...

Well you can delete from a view if that is what you are asking, but you can't have a view that deletes information. The view is a portion of data from the underlying tables. Provided that you have permissions, you can do the same data manipulation in views that you can do to a table directly.

So you can do something like:

DELETE FROM my_View WHERE id = 3; 

When to use views
What is a view

What if I do following

Create view table1_View as select * from table1 go delete from table1_view 

I checked and this command delete all data from table1

**

Using Views to Update Data:

** A view can be used in a query that updates data, subject to a few restrictions. Bear in mind that a view is not a table and contains no data—the actual modification always takes place at the table level. Views cannot be used as a mechanism to override any constraints, rules, or referential integrity defined in the base tables.

Restrictions on Updating Data Through Views You can insert, update, and delete rows in a view, subject to the following limitations:

If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.

You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.

All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.

Text and image columns can't be modified through views.

There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view. For more information Check this Article

In addition to the limited updating allowed on the view itself, you can use an INSTEAD OF trigger to perform much more involved changes. INSTEAD OF basically lets you intercept the update or delete, and perform almost any change. References: MSDN Article.

More explanation on Srinivas answer on the point of..

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

There is a workaround for this using INSTEAD OF of triggers as long as you can manage your base table structure. INSTEAD OF triggers allow you to override an INSERT, UPDATE, or DELETE operation on a view. For example, you might define an INSTEAD OF INSERT trigger on a view to replace the standard INSERT statement.

Assume you created the below view:

CREATE VIEW AuthorsNames AS SELECT au_id, au_fname, au_lname FROM authors 

You might want to insert data to columns not visible in the view. To do so, create an INSTEAD OF trigger on the view to handle inserts.

CREATE TRIGGER ShowInsert on AuthorsNames INSTEAD OF INSERT AS BEGIN INSERT INTO authors SELECT address, au_fname, au_id, au_lname, city, contract, phone, state, zip FROM inserted END 

Using this method you can insert into multiple tables but this becomes more complex if you are dealing with lot of underlying tables. Ref MSDN

The Answer lies on if the view is an UPDATABLE VIEW or a NON UPDATABLE VIEW.

An updatable view is a view which contains all the not null columns from the underlying table.

If so , ur update , delete and insert query on the view affects the data on the underlying real table .

Thats it .....