Kbase P132652: SQL: Can rows of an SQL view be updated, deleted or added?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/05/2010 |
|
Status: Verified
GOAL:
SQL: Can rows of an SQL view be updated, deleted or added?
GOAL:
What is a deletable view?
GOAL:
What is a updatable view?
GOAL:
What is a insertable view?
GOAL:
SQL: Is an SQL view deletable?
GOAL:
Is an SQL view updatable?
GOAL:
Is an SQL view insertable?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
Yes, rows of SQL Deleteable Views can be updated, deleted and added.
1. Deleteable Views: A view is said to be Deleteable if rows from that view can be deleted. A view is Deleteable if its definition satisfies the following conditions:
a. The first FROM clause contains only one table reference or one view reference.
b. There are no aggregate functions, DISTINCT clause, GROUP BY clause, or HAVING clause in the view definition.
c. If the first FROM clause contains a view reference, then the view referred to is deletable.
Example: The following VIEW definition creates a Deleteable view because the first FROM clause contains only one table reference and there are no aggregate functions, DISTINCT clause, GROUP BY clause, or HAVING clause in the view definition
CREATE VIEW Customer_View
(V_CustNum, V_Name, V_Balance)
AS
SELECT CAST(CustNum AS VARCHAR(6)), NAME, Balance
FROM
PUB.Customer
The following SQL statement deletes all rows of the view, and hence the underlying table, WHERE V_Name = 'Alpha':
DELETE FROM Customer_View WHERE V_Name = 'Alpha'
The above definition creates a Deleteable but not an Updatable view because the expression CAST(CustNum AS VARCHAR(6)) is not a simple column reference.
2. Updatable Views: A view is said to be Updatable if rows from that view can be updated. A view is Updatable if its definition satisfies the following conditions:
a. The view is deletable, that is its definition satisfies all the conditions specified above for the Deleteable view.
b. All the expressions in the first SELECT clause of the view definition are simple column references.
c. If the first FROM clause contains a view reference, then the view referred to is Updatable.
Example: The following VIEW definition creates a Updatable view because it is Deleteable all the expressions in the first SELECT clause are simple column references:
CREATE VIEW Customer_View
(V_CustNum, V_Name, V_Balance)
AS
SELECT CustNum, NAME, Balance
FROM
PUB.Customer
The following SQL statement updates the V_Name column of all rows of the view, and hence the corresponding fields and rows of the underlying table, WHERE V_CustNum = 100:
UPDATE Customer_View SET V_Name = 'Alpha' WHERE V_CustNum = 100
3. Insertable Views: A view is said to be Insertable if rows can be inserted into that view. A view is Insertable if its definition satisfies the following conditions:
a. The view is Updatable, that is its definition satisfies all the conditions specified above for the Updatable view.
b. If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
c. If the first FROM clause contains a view reference, then the view referred to is Insertable.
Example: The follow.ing VIEW definition creates an Insertable view because the underlying table has no NOT NULL columns. If any of the underlying customer table fields is defined as a NOT NULL column then that column MUST be included in the view definition make it Insertable:
CREATE VIEW Customer_View
(V_CustNum, V_Name, V_Balance)
AS
SELECT CustNum, NAME, Balance
FROM
PUB.Customer.