Updatable views

An updatable view is a special case of a deletable view. A deletable view becomes an updatable view when at least one of its columns is updatable.

A column of a view is updatable when all of the following rules are true:
  • The view is deletable.
  • The column resolves to a column of a table (not using a dereference operation) and the READ ONLY option is not specified.
  • All the corresponding columns of the operands of a UNION ALL have exactly matching data types (including length or precision and scale) and matching default values if the fullselect of the view includes a UNION ALL.
The following example uses constant values that cannot be updated. However, the view is a deletable view and at least one of its columns is updatable. Therefore, it is an updatable view.
    CREATE VIEW updatable_view
      (number, current_date, current_time, temperature)
      SELECT number, CURRENT DATE, CURRENT TIME, temperature)
    FROM weather.forecast
    WHERE number = 300