UPDATE Statement, DELETE Statement and Views

UPDATE Statement and a View

A view can be used with the UPDATE statement as if it were a base table. When a view is used to modify rows, the content of the underlying base table is actually modified.

Example 11.14 creates a view that is then used to modify the works_on table.

USE sample;
GO
CREATE VIEW v_p1
	AS SELECT emp_no, job
		FROM works_on
		WHERE project_no = 'p1';
GO

UPDATE v_p1
	SET job = NULL
	WHERE job = 'Manager';Code language: PHP (php)

You can think of updating the view in Example 11.14 as if the UPDATE statement were transformed into the following equivalent statement:

UPDATE works_on
	SET job = NULL
	WHERE job = 'Manager'
	AND project_no = 'p1'Code language: PHP (php)

WITH CHECK OPTION has the same logical meaning for the UPDATE statement as it has for the INSERT statement. Example 11.15 shows the use of WITH CHECK OPTION with the UPDATE statement.

USE sample;
GO
CREATE VIEW v_100000
	AS SELECT project_no, budget
		FROM project
		WHERE budget > 100000
		WITH CHECK OPTION;
GO

UPDATE v_100000
	SET budget = 93000
	WHERE project_no = 'p3';Code language: PHP (php)

In Example 11.15, the Database Engine tests whether the modified value of the budget column evaluates to TRUE for the condition in the WHERE clause of the SELECT statement. The attempted modification fails because the condition is not met—that is, the value 93000 is not greater than the value 100000.

The modification of columns in the underlying tables is not possible if the corresponding view contains any of the following features:

• The FROM clause in the view definition involves two or more tables and the column list includes columns from more than one table
• A column of the view is derived from an aggregate function
• The SELECT statement in the view contains the GROUP BY clause or the DISTINCT option
• A column of the view is derived from a constant or an expression

Example 11.16 shows a view that cannot be used to modify row values in the underlying base table.

USE sample;
GO
CREATE VIEW v_uk_pound (project_number, budget_in_pounds)
	AS SELECT project_no, budget*0.65
		FROM project
		WHERE budget > 100000;
GO

SELECT *
	FROM v_uk_pound;Code language: PHP (php)

The result is

The v_uk_pound view in Example 11.16 cannot be used with an UPDATE statement (nor with an INSERT statement) because the budget_in_pounds column is calculated using an arithmetic expression, and therefore does not represent an original column of the underlying table.

DELETE Statement and a View

A view can be used to delete rows of a table that it depends on, as shown in Example 11.17.

USE sample;
GO
CREATE VIEW v_project_p1
	AS SELECT emp_no, job
		FROM works_on
		WHERE project_no = 'p1';
GO

DELETE FROM v_project_p1
	WHERE job = 'Clerk';Code language: PHP (php)

Example 11.17 creates a view that is then used to delete rows from the works_on table. The deletion of rows in the underlying tables is not possible if the corresponding view contains any of the following features:

• The FROM clause in the view definition involves two or more tables and the column list includes columns from more than one table
• A column of the view is derived from an aggregate function
• The SELECT statement in the view contains the GROUP BY clause or the DISTINCT option

In contrast to the INSERT and UPDATE statements, the DELETE statement allows the existence of a constant or an expression in a column of the view that is used to delete rows from the underlying table.

Example 11.18 shows a view that can be used to delete rows, but not to insert rows or modify column values.

USE sample;
GO
CREATE VIEW v_budget (budget_reduction)
	AS SELECT budget*0.9
		FROM project;
GO

DELETE FROM v_budget;Code language: PHP (php)

The DELETE statement in Example 11.18 deletes all rows of the project table, which is referenced by the v_budget view.