Can we write update and delete queries in views?

Question

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

Answer 1

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 ...

Answer 2

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