SQL Views   «Prev  Next»
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

CustomerIDPhoneNumber

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

Try this exercise to create a view and then use it to update a table.
Update View - Exercise