Deletable views

There are a few rules that must be followed for a view to be considered deletable:

• Each FROM clause of the outer fullselect identifies only one table (with no OUTER clause), deletable view (with no OUTER clause), deletable nested table expression, or deletable common table expression.
• The database manager needs to be able to derive the rows to be deleted in the table using the view definition. Certain operations make this impossible
  • A grouping of multiple rows into one using a GROUP BY clause or column functions result in a loss of the original row and make the view non deletable.
  • Similarly when the rows are derived from a VALUES there is no table to delete from. Again the view is not deletable.
• The outer fullselect doesn't use the GROUP BY or HAVING clauses.
• The outer fullselect doesn't include column functions in its select list.
• The outer fullselect doesn't use set operations (UNION, EXCEPT, or INTERSECT) with the exception of UNION ALL
• The tables in the operands of a UNION ALL must not be the same table, and each operand must be deletable.
• The select list of the outer fullselect does not include DISTINCT.

A view must meet all the rules listed previously to be considered a deletable view. For example, the following view is deletable. It follows all the rules for a deletable view.

    CREATE VIEW deletable_view
      (number, date, start, end)
    AS
      SELECT number, date, start, end
      FROM employee.summary
      WHERE date='01012007'