Update a view

You can use an UPDATE statement on a modifiable view. However, the database server does not support updating any derived column. A derived column is a column produced by an expression in the select list of the CREATE VIEW statement (for example, order_date + 30).

The following example shows a modifiable view that contains a derived column and an UPDATE statement that can be accepted against it:

CREATE    VIEW   response(user_id, received, resolved, duration) AS
   SELECT user_id, call_dtime, res_dtime,  res_dtime - call_dtime
      FROM cust_calls
      WHERE user_id = USER;

UPDATE response SET resolved = TODAY
   WHERE resolved IS NULL;

You cannot update the duration column of the view because it represents an expression (the database server cannot, even in principle, decide how to distribute an update value between the two columns that the expression names). But if no derived columns are named in the SET clause, you can perform the update as if the view were a table.

A view can return duplicate rows even though the rows of the underlying table are unique. You cannot distinguish one duplicate row from another. If you update one of a set of duplicate rows (for example, if you use a cursor to update WHERE CURRENT), you cannot be sure which row in the underlying table receives the update.