Updating Through a View
Go Up to Updating Data (Embedded SQL Guide)
Existing rows can be updated through a view if the following conditions are met:
- The view is updatable. For a complete discussion of updatable views, see the Data Definition Guide.
- The view is created using the
WITH CHECK OPTION.
- A user or stored procedure has
UPDATEprivilege for the view.
Values can only be updated through a view for those columns named in the view. For example, suppose the view,
PART_DEPT, is defined as follows:
EXEC SQL CREATE VIEW PART_DEPT (DEPARTMENT, NUMBER, BUDGET) AS SELECT DEPARTMENT, DEPT_NO, BUDGET FROM DEPARTMENT WITH CHECK OPTION;
PART_DEPT references a single table, data can be updated for the columns named in the view. The
WITH CHECK OPTION assures that all values entered through the view fall within ranges prescribed for each column when the
DEPARTMENT table was created. For example, the following statement updates the budget of the Publications department through the
EXEC SQL UPDATE PART_DEPT SET BUDGET = 2505700 WHERE DEPARTMENT = 'Publications';
- Note: See “Working with Triggers” in the Data Definition Guide for tips on using triggers to update non-updatable views.