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 UPDATE privilege 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;

Because 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 PART_DEPT view:

EXEC SQL
UPDATE PART_DEPT
SET BUDGET = 2505700
WHERE DEPARTMENT = 'Publications';

For information about creating a view, see Working with Data Definition Statements. For the complete syntax of CREATE VIEW, see the Language Reference Guide.

Note:
See “Working with Triggers” in the Data Definition Guide for tips on using triggers to update non-updatable views.

Advance To:

• Specifying Transaction Names in UPDATE