Lesson 9 | Updating a table with a view |
Objective | Create view, then create UPDATE statement to modify values |
Use View to update Information in Underlying Tables
Updating Table with a view
With some engines, you can even protect the
SELECT
statement associated with your view. This lets you set up the view, but at the same time make sure that people cannot use your view to figure out the underlying tables. If you are working with confidential information, you will want to look into encrypting your views to protect them. Views can be updated only if all elements of the view are a subset of the base table.
Suppose there exists a base table
empid name and addr columns
then a view on this table is created as
create view myview as
select * from emp;
only these type of views are updatable.
create view myview as select name addr from emp;
is not updatable.
Suppose you have the following table named PhoneNumbers, in which CustomerID is a unique identifier:
PhoneNumbers Table with primary key CustomerID
Also suppose you have the following view defined:
CREATE VIEW MyView
AS SELECT PhoneNumber
FROM PhoneNumbers
This view would be read-only because it is not possible to understand which row to update. You've left out the CustomerID, which is the key to the table. If, however, you add the key (CustomerID) to the view definition, you will be able to update it because each row would have only one possible occurrence based on this ID. Both of these will be updateable:
CREATE VIEW
MyView AS
SELECT
CustomerID, PhoneNumber from PhoneNumbers
or
CREATE VIEW
MyView AS
SELECT * FROM PhoneNumbers
Update View - Exercise