I am using Microsoft SQL Enterprise Manager version 8.0 and have
created a view from a combination of 4 different tables. I would like
to be able to go into sql and open the view and select a row and
delete that row however this seem impossible right now. I am not sure
if it's possible to delete a row from a view?? Or could it be that
these tables are all interconnected and in order to delete a record
that is joined to one or more of the tables it has to be deleted at
the top level of the join heirarchy etc etc. (do you understand what i
mean?) Can this be done??
Thanks in advance,
ErinXmlAdoNewbie (erin.sebastian@.cowaninsurancegroup.com) writes:
> I am using Microsoft SQL Enterprise Manager version 8.0 and have
> created a view from a combination of 4 different tables. I would like
> to be able to go into sql and open the view and select a row and
> delete that row however this seem impossible right now. I am not sure
> if it's possible to delete a row from a view?? Or could it be that
> these tables are all interconnected and in order to delete a record
> that is joined to one or more of the tables it has to be deleted at
> the top level of the join heirarchy etc etc. (do you understand what i
> mean?) Can this be done??
So if you have a view which is like:
create view petra (a1, b1, b2, c) as
SELECT n.a, n.b, k.b, c
from nisse n join kalle k on n.a = k.a
go
And you say "DELETE petra WHERE a1 = 2" what do you expect to happen?
From which table is the row to be deleted?
Anyway, Books Online says in the topic for DELETE:
view_name
Is the name of a view. The view referenced by view_name must be
updatable and reference exactly one base table in the FROM clause of
the view. For more information about updatable views, see CREATE VIEW.
Thus, it does not seem from your description that your view would
be updatable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 7 Jun 2004 11:39:38 -0700, XmlAdoNewbie wrote:
> Hi All,
> I am using Microsoft SQL Enterprise Manager version 8.0 and have
> created a view from a combination of 4 different tables. I would like
> to be able to go into sql and open the view and select a row and
> delete that row however this seem impossible right now. I am not sure
> if it's possible to delete a row from a view?? Or could it be that
> these tables are all interconnected and in order to delete a record
> that is joined to one or more of the tables it has to be deleted at
> the top level of the join heirarchy etc etc. (do you understand what i
> mean?) Can this be done??
> Thanks in advance,
> Erin
As Mr. Sommarskog explained, it is not technically possible to delete from
a view based on more than one table, and the reason is that it is unclear
what record should actually be deleted. However, if it is clear to you,
then you can do something like the following.
CREATE TABLE1 ( KEY1 int PRIMARY KEY,
NAME varchar(30) NOT NULL,
KEY2 int NOT NULL,
KEY3 int NOT NULL,
KEY4 int NOT NULL);
CREATE TABLE2 ( KEY2 int PRIMARY KEY, CITY varchar(30) NOT NULL);
CREATE TABLE3 ( KEY3 int PRIMARY KEY, STATE varchar(2) NOT NULL);
CREATE TABLE4 ( KEY4 int PRIMARY KEY, MARKET varchar(30) NOT NULL);
CREATE VIEW1 AS
SELECT TABLE1.KEY1, TABLE1.NAME, TABLE2.CITY, TABLE3.STATE, TABLE4.MARKET
FROM TABLE1
INNER JOIN TABLE2 on TABLE1.KEY2 = TABLE2.KEY2
INNER JOIN TABLE3 on TABLE1.KEY3 = TABLE2.KEY3
INNER JOIN TABLE4 on TABLE1.KEY4 = TABLE2.KEY4;
Now, suppose what you want to do is delete all rows from TABLE1 that match
TABLE4.MARKET='Orlando':
DELETE FROM TABLE1
WHERE TABLE1.KEY1 IN (
SELECT VIEW1.KEY1 FROM VIEW1 WHERE VIEW1.MARKET = 'Orlando' );
So, you see that you're not deleting directly from the view; you're
deleting from a table that participates in the view, based on information
you retrieved from the view.|||You can also define an INSTEAD OF TRIGGER on the view and delete the
records from the two tables in the trigger definition. This is
probably the cleanest way to do it and gives you the perception of
being able to delete from the view.
Ross Presser <rpresser@.imtek.com> wrote in message news:<143lxsb87s5gp.1l5wdsmlcexee$.dlg@.40tude.net>...
> On 7 Jun 2004 11:39:38 -0700, XmlAdoNewbie wrote:
> > Hi All,
> > I am using Microsoft SQL Enterprise Manager version 8.0 and have
> > created a view from a combination of 4 different tables. I would like
> > to be able to go into sql and open the view and select a row and
> > delete that row however this seem impossible right now. I am not sure
> > if it's possible to delete a row from a view?? Or could it be that
> > these tables are all interconnected and in order to delete a record
> > that is joined to one or more of the tables it has to be deleted at
> > the top level of the join heirarchy etc etc. (do you understand what i
> > mean?) Can this be done??
> > Thanks in advance,
> > Erin
> As Mr. Sommarskog explained, it is not technically possible to delete from
> a view based on more than one table, and the reason is that it is unclear
> what record should actually be deleted. However, if it is clear to you,
> then you can do something like the following.
> CREATE TABLE1 ( KEY1 int PRIMARY KEY,
> NAME varchar(30) NOT NULL,
> KEY2 int NOT NULL,
> KEY3 int NOT NULL,
> KEY4 int NOT NULL);
> CREATE TABLE2 ( KEY2 int PRIMARY KEY, CITY varchar(30) NOT NULL);
> CREATE TABLE3 ( KEY3 int PRIMARY KEY, STATE varchar(2) NOT NULL);
> CREATE TABLE4 ( KEY4 int PRIMARY KEY, MARKET varchar(30) NOT NULL);
> CREATE VIEW1 AS
> SELECT TABLE1.KEY1, TABLE1.NAME, TABLE2.CITY, TABLE3.STATE, TABLE4.MARKET
> FROM TABLE1
> INNER JOIN TABLE2 on TABLE1.KEY2 = TABLE2.KEY2
> INNER JOIN TABLE3 on TABLE1.KEY3 = TABLE2.KEY3
> INNER JOIN TABLE4 on TABLE1.KEY4 = TABLE2.KEY4;
> Now, suppose what you want to do is delete all rows from TABLE1 that match
> TABLE4.MARKET='Orlando':
> DELETE FROM TABLE1
> WHERE TABLE1.KEY1 IN (
> SELECT VIEW1.KEY1 FROM VIEW1 WHERE VIEW1.MARKET = 'Orlando' );
> So, you see that you're not deleting directly from the view; you're
> deleting from a table that participates in the view, based on information
> you retrieved from the view.|||Thanks Everyone for your help! I have decided to build a little
utility in C# that will allow the user to pick the record to delete
from the view and then delete the record from the table and all joined
tables.
I appreciate all your help!
Erin
