Updates on Views
Only a limited set of updates on views is supported because of problems that can occur. Updates are not supported on views that have more than one base table, or on any column whose source is not a simple column name (for example, set functions or computations). If the view was created using the With Check Option enabled in the Create View dialog, no updates or inserts are allowed on columns that are in the qualification of the view definition. For more information on the With Check Option control, see online help for the Create View dialog.
Updating is supported only if it can be guaranteed (without looking at the actual data) that the result of updating the view is identical to that of updating the corresponding base table.
Note: Updating, deleting, or inserting data in a table using views is not recommended. You cannot update or insert into a view with Query-By-Forms. You can update, delete, or insert with SQL statements, but you must abide by the following rules, keeping in mind that an error occurs if you attempt an operation that is not permitted.
Types of Updates Not Permitted on Views
You cannot perform the following types of updates on a view:
• One that involves a column that is a set function (aggregate) or derived from a computational expression
In the following example of a SELECT statement used to define a view, you cannot update the tsal column because it is a set function:
SELECT dept, SUM(sal) AS tsal
FROM deptinf GROUP BY dept
• One that causes more than one table to be updated
Consider the following example of a SELECT statement used to define a view:
SELECT e.name, e.dept, e.div, d.bldg
FROM emp e, deptinf d
WHERE e.dept = d.dname AND e.div = d.div
Updates to this data must be done through the underlying base tables, not this view.
• You can update a column that appears in the qualification of a view definition, as long as the update does not cause the row to disappear from the view. For example, if the WHERE clause is as follows, update the deptno from 5 to 8, but not from 5 to 20:
WHERE deptno < 10
Last modified date: 01/30/2023