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

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.

For Dynamic Server, 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 (IDS).