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

MyView AS
CustomerID, PhoneNumber from PhoneNumbers

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

SEMrush Software