Creating a View for update

Go Up to Creating a View

An updatable view is one that enables privileged users to insert, update, and delete information in the view’s base table. To be updatable, a view must meet the following conditions:

• It derives its columns from a single table or updatable view.
• It does not define a self-join of the base table.
• It does not reference columns derived from arithmetic expressions.
• The view’s SELECT statement does not contain:

• A WHERE clause that uses the DISTINCT predicate
• A HAVING clause
• Functions
• Nested queries
• Stored procedures

In the following view, HIGH_CITIES is an updatable view. It selects all cities in the CITIES table with altitudes greater than or equal to a half mile.

EXEC SQL
CREATE VIEW HIGH_CITIES AS
SELECT CITY, COUNTRY_NAME, ALTITUDE FROM CITIES
WHERE ALTITUDE >= 2640;
EXEC SQL
COMMIT;

Users who have INSERT and UPDATE privileges for this view can change rows in or add new rows to the view’s underlying table, CITIES. They can even insert or update rows that cannot be displayed by the HIGH_CITIES view. The following INSERT adds a record for Santa Cruz, California, altitude 23 feet, to the ­CITIES table:

EXEC SQL
INSERT INTO HIGH_CITIES (CITY, COUNTRY_NAME, ALTITUDE)
VALUES ('Santa Cruz', 'United States', '23');

To restrict inserts and updates through a view to only those rows that can be selected by the view, use the WITH CHECK OPTION in the view definition. For example, the following statement defines the view, HIGH_CITIES, to use the WITH CHECK OPTION. Users with INSERT and UPDATE privileges will be able to enter rows only for cities with altitudes greater than or equal to a half mile.

EXEC SQL
CREATE VIEW HIGH_CITIES AS
SELECT CITY, COUNTRY_NAME, ALTITUDE FROM CITIES
WHERE ALTITUDE > 2640 WITH CHECK OPTION;