Can we modify the table data through SQL View?
The answer is: Yes !
We can modify the data of an underlying base table through a view, in the same manner as we modify data in a table by using UPDATE, INSERT and DELETE statements or by using the BCP utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:
·
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:
1. An
aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and
VARP).
2. 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.
