8.7. Updatable Views
Once a view has been created from a physical table, it can then be used to modify the view’s data of a single underlying table. Essentially when a view is updated the changes pass through the view to the underlying base table. A view designed in this manner is called an updatable view and can have INSERT, UPDATE, and DELETE operations performed into the single table from which it’s constructed.
Because views are dependent on getting their data from a base table and have no physical existence of their own, you should exercise care when constructing an updatable view. Although useful in modifying the rows in a table, updatable views do have a few limitations that programmers and users should be aware of.
First, an updatable view can only have a single base table associated with it. This means that the underlying table cannot be used in a join operation or with any set operators. Because an updatable view has each of its rows associated with just a single row in an underlying table, any operations involving two or more tables will produce an error and result in update operations not being performed.
An updatable view cannot contain a subquery. A subquery is a complex query consisting of a SELECT statement contained inside another statement. This violates the rules for updatable views and is not allowed.
An updatable view can update a column using a view’s column alias, but cannot contain the DISTINCT keyword, have any aggregate (summary) functions, calculated columns, or derived columns associated with it. Because these columns are produced by an expression, they are not allowed.
Finally, an updatable view can contain a WHERE clause but not other clauses such as ORDER BY, GROUP BY, or HAVING.
In the remaining sections, three types of updatable views will be examined:
views that insert one or more rows of data,
views that update existing rows of data, and
views that delete one or more rows of data from a single underlying table.
8.7.1. Inserting New Rows of Data
You can add or insert new rows of data in a view using the INSERT INTO statement. Suppose we have a view consisting of only software products called SOFTWARE_PRODUCTS_VIEW. The PROC SQL code used to create this view consists of a SELECT statement with a WHERE clause. There are four defined columns: product name, product number, product type, and product cost, in that order. When accessed from the SAS Windowing environment, the view produces the results displayed below.
SQL Code
PROC SQL;
CREATE VIEW SOFTWARE_PRODUCTS_VIEW AS
SELECT prodnum, prodname, prodtype, prodcost
FORMAT=DOLLAR8.2
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'),
QUIT;
Suppose you want to add a new row of data to this view. This can be accomplished by specifying the corresponding values in a VALUES clause as follows.
SQL Code
PROC SQL; INSERT INTO SOFTWARE_PRODUCTS_VIEW VALUES(6002,'Security Software','Software',375.00); QUIT;
As seen from the view results, the INSERT INTO statement added the new row of data corresponding to the WHERE logic in the view. The view contains the new row and consists of the value 6002 in product number, “Security Software” in product name, “Software” in product type, and $375.00 in product cost, as shown.
View Results
As depicted in the table results, the new row of data was added to the PRODUCTS table using the view called SOFTWARE_PRODUCTS_VIEW. The new row in the PRODUCTS table contains the value 6002 in product number, “Security Software” in product name, “Software” in product type, and $375.00 in product cost. The manufacturer number column is assigned a null value (missing value), as shown.
Table Results
Now let’s see what happens when a row of data is added through a view that does not meet the condition(s) in the WHERE clause in the view. Suppose we want to add a row of data containing the value 1701 for product number, “Travel Laptop SE” in product name, “Laptop” in product type, and $4200.00 in product cost in the SOFTWARE_PRODUCTS_VIEW view.
SQL Code
PROC SQL; INSERT INTO SOFTWARE_PRODUCTS_VIEW VALUES(1701,'Travel Laptop SE','Laptop',4200.00); QUIT;
Because the new row’s value for product type is “Laptop”, this value violates the WHERE clause condition when the view SOFTWARE_PRODUCTS_VIEW was created. As a result, the new row of data is rejected and is not added to the table PRODUCTS. The SQL procedure also prevents the new row from appearing in the view because the base table controls what the view contains.
The updatable view does exactly what it is designed to do — that is, validate each new row of data as each row is added to the base table. Whenever the WHERE clause condition is violated the view automatically rejects the row as invalid and restores the table to its pre-updated state by rejecting the row in error and deleting all successful inserts before the error occurred. In our example, we see the following error message was issued to the SAS log to confirm that the view was restored to its original state before the update took place.
SAS Log Results
PROC SQL;
INSERT INTO PRODUCTS_VIEW
VALUES(1701,'Travel Laptop SE','Laptop'
|
,4200.00);
ERROR: The new values do not satisfy the view's
Views will not accept new rows added to a base table when the number of columns in the VALUES clause does not match the number of columns defined in the view, unless the columns that are being inserted are specified. In the next example, a partial list of columns for a row of data is inserted with a VALUES clause. Because the inserted row of data does not contain a value for product cost, the new row will not be added to the PRODUCTS table. The resulting error message indicates that the VALUES clause has fewer columns specified than exist in the view itself, as shown in the SAS log below.
SQL Code
PROC SQL; INSERT INTO SOFTWARE_PRODUCTS_VIEW VALUES(6003,'Cleanup Software','Software'), QUIT;
SAS Log Results
PROC SQL;
INSERT INTO SOFTWARE_PRODUCTS_VIEW
VALUES(6003,'Cleanup Software','Software'),
ERROR: VALUES clause 1 attempts to insert fewer
|
Suppose a view called SOFTWARE_PRODUCTS_TAX_VIEW was created with the sole purpose of deriving each software product’s sales tax amount as follows.
SQL Code
PROC SQL;
CREATE VIEW SOFTWARE_PRODUCTS_TAX_VIEW AS
SELECT prodnum, prodname, prodtype, prodcost,
prodcost * .07 AS Tax
FORMAT=DOLLAR8.2 LABEL='Sales Tax'
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'),
QUIT;In the next example, an attempt is made to add a new row through the SOFTWARE_PRODUCTS_TAX_VIEW view by inserting a VALUES clause with all columns defined. The row is rejected and an error produced because an update was attempted against a view that contains a computed (calculated) column. Although the VALUES clause contains values for all columns defined in the view, the reason the row is not inserted into the PRODUCTS table is due to the reference to a computed (or derived) column TAX (Sales Tax) as shown in the SAS log results.
SQL Code
PROC SQL; INSERT INTO SOFTWARE_PRODUCTS_TAX_VIEW VALUES(6003,'Cleanup Software','Software',375.00,26.25); QUIT;
PROC SQL;
INSERT INTO SOFTWARE_PRODUCTS_TAX_VIEW
VALUES(6003,'Cleanup Software','Software'
|
8.7.2. Updating Existing Rows of Data
The SQL procedure permits rows to be updated through a view. The data manipulation language statement that is specified to modify existing data in PROC SQL is the UPDATE statement. Suppose a view were created to select only laptops from the PRODUCTS table. The SQL procedure code used to create the view is called LAPTOP_PRODUCTS_VIEW and consists of a SELECT statement with a WHERE clause. There are four defined columns: product name, product number, product type, and product cost, in that specific order. When accessed, the view produces the results displayed below.
SQL Code
PROC SQL;
CREATE VIEW LAPTOP_PRODUCTS_VIEW AS
SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) = 'LAPTOP';
QUIT;Results
In the next example, all laptops are to be discounted by twenty percent and the new price is to take effect immediately. The changes applied through the LAPTOP_PRODUCTS_VIEW view computes the discounted product cost for “Laptop” computers in the PRODUCTS table using an UPDATE statement with corresponding SET clause.
SQL Code
PROC SQL; UPDATE LAPTOP_PRODUCTS_VIEW SET PRODCOST = PRODCOST – (PRODCOST * 0.2); QUIT;
SAS Log Results
PROC SQL;
UPDATE LAPTOP_DISCOUNT_VIEW
SET PRODCOST = PRODCOST - (PRODCOST * 0.2);
NOTE: 1 row was updated in WORK.LAPTOP_DISCOUNT_VIEW.
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.04 seconds |
Results
Sometimes updates applied through a view can change the rows of data in the base table so that once the update is performed the rows in the base table no longer meet the criteria in the view. When this occurs, the changed rows of data cannot be displayed by the view. Essentially the updated rows matching the conditions in the WHERE clause no longer match the conditions in the view’s WHERE clause after the updates are made. As a result, the view updates the rows with the specified changes but is no longer able to display the rows of data that were changed.
Suppose a view were created to select laptops costing more than $2,800 from the PRODUCTS table. The SQL procedure code used to create the view called LAPTOP_DISCOUNT_VIEW consists of a SELECT statement with a WHERE clause.
There are four defined columns: product name, product number, product type, and product cost, in that order. When accessed, the view produces the results displayed below.
SQL Code
PROC SQL;
CREATE VIEW LAPTOP_DISCOUNT_VIEW AS
SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM PRODUCTS
WHERE UPCASE(PRODTYPE) = 'LAPTOP' AND
PRODCOST > 2800.00;
QUIT;Results
The next example illustrates how updates are applied through a view in the Windows environment so the rows in the table no longer meet the view’s criteria. Suppose a twenty percent discount is applied to all laptops. An UPDATE statement and SET clause are specified to allow the rows in the Products table to be updated through the view. Once the update is performed and the view is accessed, a dialog box appears indicating that no rows are available to display because the data from the PRODUCTS table no longer meets the view’s WHERE clause expression.
SQL Code
PROC SQL; UPDATE LAPTOP_DISCOUNT_VIEW SET PRODCOST = PRODCOST – (PRODCOST * 0.2); QUIT;
PROC SQL;
UPDATE LAPTOP_DISCOUNT_VIEW
SET PRODCOST = PRODCOST - (PRODCOST * 0.2);
NOTE: 1 row was updated in WORK.LAPTOP_DISCOUNT_VIEW.
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.06 seconds |
Results
8.7.3. Deleting Rows of Data
Now that you have seen how updatable views can add or modify one or more rows of data, you may have a pretty good idea how to create an updatable view that deletes one or more rows of data. Consider the following updatable view that deletes manufacturers whose manufacturer number is 600 from the underlying PRODUCTS table.
SQL Code
PROC SQL; DELETE FROM SOFTWARE_PRODUCTS_VIEW WHERE MANUNUM=600; QUIT;
PROC SQL;
DELETE FROM SOFTWARE_PRODUCTS_VIEW
WHERE MANUNUM=600;
NOTE: 2 rows were deleted from WORK
|