Following operations may be done on VIEWS after creating it:
A VIEW can be updated using UPDATE clause. However constraints to update the VIEW are:
- VIEW should not have referred to more than one database.
- SELECT statements should not have any computed columns.
- SELECT statements should not use GROUP BY, HAVING, DISTINCT clauses.
- SELECT statements must not refer to the read only views.
Example of updating a field in a view is:
CREATE VIEW students AS
SELECT name, rollNo, marks FROM tblstudents;
To update a student's marks with rollNo 6 we may do:
SET marks=90 WHERE rollNo=6;
To display a definition of the view we use SHOW statement:
SHOW CREATE VIEW viewName;
For example, to see the details of patInfo view we say:
SHOW CREATE VIEW patInfo;
Views may be deleted using this syntax:
DROP VIEW viewName;
The real-time example dropping application is:
DROP VIEW vwProduct;