is view in database updatable ? if yes , how ? if no, why ?
+1
A:
Yes they are - the syntax is the same as updating a table
Update MyView
Set Col1 = "Testing"
Where Col2 = 3
Go
There a few conditions to creating an View that can be updated. They can be found here
EDIT:
I must add that is based on MS SQL
Barry
2010-09-23 11:39:25
Why `go`? I don't think it's a valid sql construct except for SSMS. :)
Denis Valeev
2010-09-23 11:40:31
Barry
2010-09-23 11:43:49
@Barry I believe your query will work in other systems as well sans the go part.
Denis Valeev
2010-09-23 11:51:17
A:
Yes you can, but have a look at CREATE VIEW (Transact-SQL) and see the section Updatable Views
astander
2010-09-23 11:40:42
A:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
See Remarks\updateable view
gandjustas
2010-09-23 11:40:52
+12
A:
The actual answer is "it depends", there are no absolutes.
The basic criteria is it has to be an updateable view in the opinion of the database engine, that is to say can the engine uniquely identify the row(s) to be updated and secondly are the fields updateable. If your view has a calculated field or represents the product of a parent/child join then the default answer is probably no.
However its also possible to cheat... in MS SQL Server and Oracle (to take just two examples) you can have triggers that fire when you attempt to insert or update a view such that you can make something that the server doesn't think updateable into something that is - usually because you have knowledge that the server can't easily infer from the schema.
Murph
2010-09-23 11:49:21
Too many years of being a "programmer" with too many conversations with clients/users where nice answers that can be represented by zeros and ones are few and far between (-:
Murph
2010-09-23 20:21:41
In Oracle, too, you can create `INSTEAD OF` triggers that will handle inserts, updates and deletes on a view.
Jeffrey Kemp
2010-09-24 03:36:27
@Jeffrey - added reference to oracle as well as MS SQL to show that its not just one db that has the appropriate triggers (been most of a decade since I did any Oracle work). I won't add more - hopefully its obvious that the list is not exclusive!
Murph
2010-09-24 06:31:34
+4
A:
The correct answer is "it depends". You can't update an aggregate column in a view for example. For Oracle views you can Google for "updatable join view" for some examples of when you can and cannot update a view.
Lord Peter
2010-09-23 11:49:58
+3
A:
PostgreSQL has RULEs to create updatable VIEWs. Check the examples in the manual to see how to use them.
Ps. In PostgreSQL a VIEW is a RULE, a select rule.
Frank Heikens
2010-09-23 12:57:10
+2
A:
In the past it wasn't possible to update any views. The main purpose of a view is to look at data, hence the name. It could also have been called a stored query.
Today, many database engines support to update views. It's bound to restrictions, some updates are virtually impossible (eg. calculated columns, group by etc).
Stefan Steinegger
2010-09-23 13:11:43
+2
A:
There are two approaches:
INSTEAD OF trigger, which basically shifts the problem to the user. You write some procedural code that does the job. Certainly, no guarantees is made about correctness, consistency, etc. From RDBMS engine perspective a trigger that deletes everything from the base tables, no matter what update is made in the view, is perfectly fine.
Much more ambitious is view updates handled exclusively by RDBMS engine. Not much progress is made here: to put it mildly, if you have some good ideas there, then you can roll out PhD thesis. In practice, your favorite RDBMS might allow some limiting ad-hock view updates; check the manual:-)
Tegiri Nenashi
2010-09-23 17:31:55