Condition for Creating Updatable VIEW
Description
To update data in a virtual table, it must be updatable because an option is needed to define data.
A virtual table is updatable if it satisfies the following conditions:
- The FROM clause must include only one table or updatable view. However, two tables included in parentheses as in FROM (class_x, class_y) can be updated because they represent one table.
- The DISTINCT or UNIQUE statement should not be included.
- The GROUP BY... HAVING statement should not be included.
- Aggregate functions such as SUM( ) or AVG( ) should not be included.
- The entire query must consist of queries that can be updated by UNION ALL, not by UNION. However, the table should exist only in one of the queries that constitute UNION ALL.
- If an row is inserted into a view created by using the UNION ALL statement, the system determines into which table the row will be inserted. This cannot be done by the user. To control this, the user must manually insert the row or create a separate view for insertion.
Even when all rules above are satisfied, each column of the a view may not be updatable. The following columns cannot be updated:
- Path expressions
- Numeric type column that includes an arithmetic operator
Even though the column defined in the view is updatable, a view can be updated only when an appropriate update authorization is granted on the table included in the FROM clause. Also there must be an access authorization to a view. The way to grant an access authorization to a view is the same to grant an access authorization to a table. For details on granting authorization, see "Granting Authorization.