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 View
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