Updating Rows Through a View

You can update data through a single-table view if you have the Update privilege on the view (see GRANT statement). For a view to be updatable, the query that defines the view must not contain any of the following items:
  • 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;
The following statements define a view that includes all the rows in the customer table and changes the customer_num value to 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.

Because duplicate rows can occur in a view even if its base table has unique rows, be careful when you update a table through a view. For example, if a view is defined on the items table and contains only the order_num and total_price columns, and if two items from the same order have the same total price, the view contains duplicate rows. In this case, if you update one of the two duplicate total_price values, you have no way to know which item price is updated.
Important: If you are using a view with a check option, you cannot update rows in a remote table.

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.