Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Updating Data in a View

By: FYIcenter.com

(Continued from previous topic...)

Can You Update Data in a View?

Can you update data in a view? The answer is no.

But if the question is "Can you update data in the underlying table through view?" The answer is then yes. SQL Server will allow you to update data in the underlying table through a view. The tutorial exercise below is a good example:

UPDATE fyi_links_top SET url = REVERSE(url) 
   WHERE id = 100001;
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
100001 1000001 moc.retneciyf.abd
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url               notes counts      created
------ ----------------- ----- ----------- ----------
100001 moc.retneciyf.abd NULL  1000001     2007-05-19

(Continued on next topic...)

1. What Are Views?
2. How To Create a View on an Existing Table?
3. How To See Existing Views?
4. How To Drop Existing Views from a Database?
5. How To Get a List of Columns in a View using "sys.columns"?
6. How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
7. How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
8. How To Generate CREATE VIEW Script on an Existing View?
9. How To Get the Definition of a View Out of the SQL Server?
10. Can You Create a View with Data from Multiple Tables?
11. Can You Create a View using Data from Another View?
12. What Happens If You Delete a Table That Is Used by a View?
13. Can You Use ORDER BY When Defining a View?
14. How To Modify the Underlying Query of an Existing View?
15. Can You Insert Data into a View?
16. Can You Update Data in a View?
17. Can You Delete Data from a View?
18. How To Assign New Column Names in a View?
19. How Column Data Types Are Determined in a View?
20. How To Bind a View to the Schema of the Underlying Tables?
21. How To Create an Index on a View?