Can data in a View be updated to update orig Table even ..?

[Mb2b]

I have not really worked much with Views ... I usually work with Tables directly ... Any help is appreciated!

Looking at a View supposedly created from a parent table .. with all the parent table fields PLUS say 3 additional colums added?

Is that possible?

Does updating data in the view update the original table data? ... I presume the 3 caluculated fields in the View which are NOT in the parent table is not a problem, right? They will be ignored by the parent table I presume since it has no knowledge of them, right?

 

[SQLDenis]

a view can be updated but only one of the underlying tables can be updated at one time

In other words if your view has 4 columns and two of them are from table A and two from table B then you can only update the columns from table A OR B with an update statement not all 4 columns

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Views in SQL Server are not like Oracle views which are more like snapshots of data at a point in time (and have to be refreshed). Instead, views in SQL Server are simply saved queries. When used, the query engine doesn't select any data from the view, it just sees the view as a sort of derived table guiding it to the actual data in the real tables the view references. You can make "materialized views" in sql server by adding a clustered index to one, but it is an advanced strategy full of serious pitfalls.

Consider the following view:

CREATE VIEW MyView
AS
SELECT
   A = T.Column1,
   B = T.Column2,
   C = X.OtherData
FROM
   Table1 T
   INNER JOIN MoreData X ON T.DataID = X.DataID

This doesn't store data anywhere. It is just saved query text. You can update this view as long as your update statement only tries to update one table in it at a time.

UPDATE MyView SET A = 1 WHERE C = 23

SQL Server will translate this to the 'real' query that is going on, see the next code block below. If you want to see this in action, run the query in Query Analyzer and turn on the show execution plan option.

UPDATE T
SET T.Column1 = 1
WHERE X.OtherData = 23
FROM
   Table1 T
   INNER JOIN MoreData X ON T.DataID = X.DataID

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]