Updating Rows Through a View
- Columns in the projection list that are aggregate values
- Columns in the projection list that use the UNIQUE or DISTINCT keyword
- A GROUP BY clause
- A UNION operator
In addition, if a view is built on a table that has a derived value for a column, that column cannot be updated through the view. Other columns in the view, however, can be updated. In an updatable view, you can update the values in the underlying table by inserting values into the view.
CREATE VIEW cust_view AS SELECT * FROM customer; UPDATE cust_view SET customer_num=10001 WHERE customer_cum=101;
10001
in any row where the value of that column
is 101
: CREATE VIEW cust_view AS SELECT * FROM customer; UPDATE cust_view SET customer_num=10001 WHERE customer_num=101;
You can use data-integrity constraints to prevent users from updating values in the underlying table when the update values do not fit the SELECT statement that defined the view. For more information, see WITH CHECK OPTION Keywords.
An alternative to directly modifying data values in a view with the UPDATE statement is to create an INSTEAD OF trigger on the view. For more information, see INSTEAD OF Triggers on Views.