Use SQL Views to Update, Insert, Select, Delete

[jsc0624]

HI Guys,

Is it possible to Update, Insert and Delete using Views? Where can I find the best tutorials for Views? Thanks in advance.

[sansknowledge]

http://en.wikipedia.org/wiki/View_(database)

[jsc0624]

Thanks but how can I create an updatable views? I am not yet familiar with Views? What are the rules to consider?

[sansknowledge]

that is based on your database and googling a little would do a lot
HTH

[NeedSomeAnswers]

Can i ask why you would want to ?

I believe that in SQL Server at least you can update (and probably delete, not sure about insert) from a view, but only if the view is querying a single table. If you view returns fields from more than 1 table then No !!

Still don't know why you would want to do this though, why not just Update/Insert/Delete normally directly into the tables ??

[Hack]

Presumably you are using SQL Server, although that has not definately been established.

Assuming it is so, however, what version of SQL Server are you using?

[szlamany]

It doesn't matter how many tables the VIEW relates to - it's based on how many tables you refer to in the INSERT or UPDATE statement

Create Table Table1 (PKCol int, DataCol varchar(10))
Go
Create Table Table2 (PKCol int, DataCol varchar(10))
Go
Create View BothTables 
as
Select T1.PKCol "PKCol1", T1.DataCol "DataCol1"
     , T2.PKCol "PKCol2", T2.DataCol "DataCol2"
     From Table1 T1
     Left Join Table2 T2 on T2.PKCol=T1.PKCol
Go
Insert into BothTables values (1,'A',1,'B')
--this gives error "View or function 'BothTables' is not updatable because the
--modification affects multiple base tables."
Go
Insert into BothTables (PKCol1,DataCol1) values (1,'A')
--this works - "(1 row(s) affected)"
Go
Insert into BothTables (PKCol2,DataCol2) values (1,'B')
--this works - "(1 row(s) affected)"
Go
Select * from BothTables
/*
this returns:
PKCol1      DataCol1   PKCol2      DataCol2
----------- ---------- ----------- ----------
1           A          1           B

(1 row(s) affected)
*/

I've created two tables above - and a VIEW that JOIN's them

Then I try to insert into the VIEW - all 4 columns - that's not allowed.

But I can insert into each "segregated" segment of the VIEW.

There is much power to this - UPDATEABLE VIEW's create an interesting method of serving data to a user that can in fact be insert'd and update'd

[NeedSomeAnswers]

Right that's really interesting to know i was under the impression that you couldn't, can you also Delete from a View too ?

So you can Insert/update a View as long as you are only updating data to 1 of the tables in the View ?

What are the advantages of doing this then ?

[szlamany]

From what I know you cannot delete - as DELETE is not column specific - thus you cannot hit just the one BASE TABLE.

The interesting thing about UPDATING and INSERTING into a VIEW is that you can give a user access to a VIEW that doesn't show all columns of the BASE TABLES. The "limiting" of access is the interesting and powerful feature.

So you give a user or an application a limited view of data - but that VIEW is still updateable - still insertable...

Not like I've ever done it myself - but I can appreciate places where you might use it...

[NeedSomeAnswers]

Right i see what you mean,

yes that could be useful, and an interesting thing to know Thanks!!