Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to do Add, delete and update using SQL View

Posted on 2012-03-15
6
Medium Priority
?
324 Views
Last Modified: 2012-04-02
I need to know that if I have sql view then can I do add, delete and update operation on it using SQL view.

If yes then how can I do this
0
Comment
Question by:ziorinfo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Assisted Solution

by:kevb258
kevb258 earned 184 total points
ID: 37724150
It should be possible.
I did this test:
1) CREATE VIEW VIEW1 AS SELECT * FROM TABLE1
2) SELECT * FROM VIEW1 (to check it was ok)
3) UPDATE VIEW1 SET COL2='ABC' where COL1=123

and this worked fine.
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 368 total points
ID: 37724329
'SQL View' as in a product or as in a database object (create view..)

When speaking of the object

See info at 'updatabble views' on following link
http://msdn.microsoft.com/en-us/library/ms187956.aspx
or a dedicated topic at
http://msdn.microsoft.com/en-us/library/ms180800.aspx
0
 
LVL 12

Assisted Solution

by:Anuradha Goli
Anuradha Goli earned 184 total points
ID: 37724339
You can perform add / delete operations on View

Craete view :
 CREATE VIEW VIEWNAME1  AS SELECT * FROM TABLE1

To insert a row into View:
 INSERT INTO VIEWNAME1 (COL1) values('TESTDATA')

To retrieve/View the data from View:
SELECT * FROM VIEWNAME1

To delete row from View:
DELETE FROM VIEWNAME1 where COL1=<colvalue>

To update the data from View:
 UPDATE VIEW1 SET COL1='NEWDATA' where COL2=<colvalue>
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 184 total points
ID: 37725395
It depends on the view definition.  SQL has to be able to trace the view column back unambiguously to a specific column in a base table.

If the view contains all the required key columns, and does not use aggregation, then typically you can use the view for modifications.

For INSERTs, the view must also contain every non-nullable column that does not have a default value specified.

Certain other restrictions apply.  For example:

http://msdn.microsoft.com/en-us/library/ms180800.aspx
0
 

Author Comment

by:ziorinfo
ID: 37728061
if the view is made by using multiple table then can I do these operation on it
0
 
LVL 25

Accepted Solution

by:
jogos earned 368 total points
ID: 37728297
<<if the view is made by using multiple table then can I do these operation on it>>
Limitations are in the documentation on the links I posted
#a37724329.

Repeating that
See info at chapter 'updatabble views' on following link
http://msdn.microsoft.com/en-us/library/ms187956.aspx
or a dedicated topic at
http://msdn.microsoft.com/en-us/library/ms180800.aspx



One that certainly apply to your multitable view
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

And if your stuck with limitations
If the restrictions described previously in this topic prevent you from modifying data directly through a view, consider the following options:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements. For more information, see Designing INSTEAD OF Triggers.

Link to learn more of that http://msdn.microsoft.com/en-us/library/ms175089.aspx


But think about why you are doing this. A multi-table view is easy to get all things with one query, a difficult query that you only have to (let) write once.  The danger of starting to update through a view is that you start using the heavy multi-join view to access and update only the data of a single base-table.  Not so peformant.
And today you want to update a name and a date (from tableA) , that's fine. Next week you yust add the update of a 3th column and it does not work. Why?? Oh, error message says something weard about non updatable view ....
Same can occur when some change on the view makes it not updatable anymore.

My suggestion:
Try to make stored procedures to insert, update and delete instead of doing it through the view. http://social.msdn.microsoft.com/search/en-us?query=create+procedure&x=0&y=0
Instead of trigger is same sollution , but neads more skill to comprehend.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question