If
you provide users with a set of views to use for data retrieval, what should
you do if the users also need to modify the same data? It might seem a bit
strange, for example, to force the users to retrieve data using a view, but
then allow them to directly modify the underlying table using update or insert
statements. For this purpose, MySQL, Oracle Database, and SQL Server all allow
you to modify data through a view, as long as you abide by certain
restrictions. A view is updatable if the following conditions are met:
-
No aggregate
functions are used (max(), min(), avg(), etc.).
No sub queries exist in the select or from clause, and any subqueries in the where clause do not refer to tables in the from clause.
The view does not utilize union, union all, or distinct.
- The from
clause includes at least one table or updatable view.
The from clause uses only inner joins if there is more than one table or view.
To demonstrate the
utility of updatable views, it might be best to start with a simple view
definition and then to move to a more complex
view.
For Example:
You are creating
the view:
Create View as
UpdateExample
AsSelect * from
Employee inner E join Employee_Location EL on E.EID=EL.EID
Then if you want to
update employee location and Employee name in one go you can write update
statement for view which in turns can update underlying table.
Update UpdatExample
Set Loc=’X’,Lastname=’nam’
Where EID=123
No comments:
Post a Comment