Filter
Top Products
Working with views
130
CREATE VIEW
viewname
AS
Inserting and deleting
on views
UPDATE, INSERT, and DELETE statements are allowed on some views, but not
on others, depending on their associated
SELECT statement.
You cannot update, insert into or delete from views in the following cases:
• Views containing aggregate functions, such as
COUNT(*)
• Views containing a GROUP BY clause in the SELECT statement
• Views containing a
UNION operation
In all these cases, there is no way to translate the
UPDATE, INSERT, or DELETE
into an action on the underlying tables.
Modifying views
You can modify a view using the ALTER VIEW statement. The ALTER VIEW
statement replaces a view definition with a new definition; it does not modify
an existing view definition.
The
ALTER VIEW statement maintains the permissions on the view.
Example
For example, to replace the column names with more informative names in the
DepartmentSize view described above, you could use the following statement:
ALTER VIEW DepartmentSize
(Dept_ID, NumEmployees)
AS
SELECT dept_ID, count(*)
FROM Employee
GROUP BY dept_ID
Permissions on views
A user may perform an operation through a view if one or more of the
following are true:
• The appropriate permission(s) on the view for the operation has been
granted to the user by a DBA.
• The user has the appropriate permission(s) on all the base table(s) for the
operation.