Union Operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Union Operator

Author

Topic

brihaupt
Starting Member

10 Posts

Posted - 2005-06-20 : 15:03:45

I am trying to delete rows from one of my MS SQL databases. I run the command:
delete MASTER_AVALON_Minus_Unsubscribe where email = '###@###'

This yeilds the following error:
Server: Msg 4417, Level 16, State 1, Line 1
Derived table 'MASTER_AVALON' is not updatable because the definition contains a UNION operator.

I was able to find the other instance of the information. It exists in a database called Avalon_Web_ContactUs. I need to remove the row from both databases. Also if it is possible to query MASTER_AVALON_Minus_Unsubscribe and find the other location of the information (so I don't have to go through all my databases manually to track down the information).

Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-20 : 15:11:19

So 'MASTER_AVALON_Minus_Unsubscribe' is a view?? and you want to delete records from a view??

Try deleting records from the source tables, where necessary of course.

I think we will need better info to be of more help... (maybe the view source code?)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-20 : 15:12:51

Read the link below my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

brihaupt
Starting Member

10 Posts

Posted - 2005-06-20 : 15:19:33

Both Avalon_Web_ContactUs and MASTER_AVALON_Minus_Unsubscribe are tables located within the same database. MASTER_AVALON_Minus_Unsubscribe pulls information from several other tables in order to build a mailing list. I need to remove several records from MASTER_AVALON_Minus_Unsubscribe. I looked at the link provided by X002548 and it seems to me I already provided the requested information. I am manully running commands from within SQL Query Analyzer. Please let me know what specific information you need and I will provide it.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-20 : 15:22:27

Again: The code for the view MASTER_AVALON_Minus_Unsubscribe would be probably be helpful...

quote:

...MASTER_AVALON_Minus_Unsubscribe are tables located within the same database. MASTER_AVALON_Minus_Unsubscribe pulls information from several other tables...

part in italics identifies the MASTER_AVALON_Minus_Unsubscribe as a view.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-20 : 15:53:12

quote:
Originally posted by brihaupt

I looked at the link provided by X002548 and it seems to me I already provided the requested information.

I don't think you did read it...

Where's the create statements, the sample data in DML form, the expected results?

Heck, you didn't even supply the sql that threw the error

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 11:47:55

I apologize for not being able to provide more information but I have recently taken over the position of IT Manager and I have to work on these databases that were developed by another person. I do not have any source code. I am using MS SQL 2000 SP3. To preform the delete I am using MS SQL Query Analyzer. The I have tried the delete command detailed in the first post on this issue. I am new to MS SQL so you will have to be more specific in what you are looking for. After ReReading Seventhnight's link it seems to me that I HAVE provided the DML that I have tried already. What I need to accomplish is to either
1. Remove specific e-mail addresses from the VIEW MASTER_AVALON_Minus_Unsubscribe
or
2. Remove specific rows from the view MASTER_AVALON_Minus_Unsubscribe and the original table.

I can manually remove the rows from the original tables using a standard delete command such as
delete from Avalon_Web_ContactUs where email = '###@###.###' but this does not affect the view MASTER_AVALON_Minus_Unsubscribe . Please let me know EXACTLY what you need to know.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 12:42:41

Open up Enterprise Manager, go to the views, double click on this view and copy that code into a new post here. That's what we need to help. If you want to use Query Analyzer, check out sp_help for the view.

Tara

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 13:21:08

Thank you tduggan. This is what you requested.

CREATE VIEW dbo.VIEW1
AS
SELECT email
FROM master.MASTER_AVALON MA
WHERE (NOT EXISTS
(SELECT *
FROM Master_Avalon_Unsubscribe MAU
WHERE MA.email = MAU.primary_email))

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 13:23:20

I beleive I have been looking at the wrong view. This is the info on another view MASTER_AVALON

CREATE VIEW master.MASTER_AVALON
AS
SELECT A.email
FROM Avalon_web_contactus A
WHERE email_update <> 'no'
UNION
SELECT B.email
FROM Avalon_Website_Brochures B
WHERE email_update <> 'no'
UNION
SELECT f.primary_email
FROM AVALON_PREF.dbo.tblPREFERENCES F
WHERE yes_no = 'yes'
UNION
SELECT k.primary_email
FROM OLD_tblpref K
WHERE yes_no = 'yes'
UNION
SELECT k.resbillto_email
FROM rezconnect_avalon K
UNION
SELECT Emails
FROM [master].[Avalon_Brochures_EMAIL] WHERE yes_no = 'yes'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 13:25:07

Is MASTER_AVALON_Minus_Unsubscribe also a view?

Tara

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 13:26:37

Yes it is a view. MASTER_AVALON_Minus_Unsubscribe and MASTER_AVALON are both views.

Brian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 13:28:36

We need the code for MASTER_AVALON_Minus_Unsubscribe as that's the one you are trying to run the DELETE against.

Tara

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 13:32:35

I actually supplied that a couple posts ago but here it is again.

CREATE VIEW dbo.VIEW1
AS
SELECT email
FROM master.MASTER_AVALON MA
WHERE (NOT EXISTS
(SELECT *
FROM Master_Avalon_Unsubscribe MAU
WHERE MA.email = MAU.primary_email))

Just some information that may be helpful MASTER_AVALON_Minus_Unsubscribe is a dbo and MASTER_AVALON is a master database.

Thanks,
Brian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 13:36:24

Well, your view code is wrong then. It should say CREATE VIEW dbo.MASTER_AVALON_Unsubscribe and not CREATE VIEW dbo.VIEW1, so that's why I asked for it.

Anyway, on to your problem. You can not delete directly from MASTER_AVALON_Unsubscribe as it is referring to MASTER_AVALON view which contains UNION operators. So in order to delete the data, you are going to need to delete it directly from the tables and not via the view. So you'll need to run repeated delete statements until you've hit all of the tables. Please see the view definitions for which tables you'll need to delete from.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 13:39:31

quote:

Just some information that may be helpful MASTER_AVALON_Minus_Unsubscribe is a dbo and MASTER_AVALON is a master database.

Your wording is a little off. dbo is the owner of MASTER_AVALON_Minus_Unsubscribe and master is the owner of MASTER_AVALON. BTW, master is a user in this case and not a database. If the view were in the master database, then you'd have to refer to it with its three part naming convention, like this master.dbo.ObjectName (where dbo is a user). Never store any user objects in the master database. You aren't doing this anyway, but I'd like to point it out since you though it was a master database.

Tara

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 13:40:24

Well I can atleast feel good knowing I didn't write the code. Thank you for your help Tara.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-21 : 14:00:26

quote:
Originally posted by brihaupt

I apologize for not being able to provide more information but I have recently taken over the position of IT Manager
quote:

Congratulations...you got nobody working for you?

quote:
and I have to work on these databases that were developed by another person. I do not have any source code.

No code? I would make sure I script out everything in Enterprise Manger AND make sure that I had a good backup strategy in place BEFORE I did anything.

quote:

I am using MS SQL 2000 SP3. To preform the delete I am using MS SQL Query Analyzer. The I have tried the delete command detailed in the first post on this issue.

I would create a backup table just in case

SELECT * INTO myBakupTable99 FROM myTable99

quote:

I am new to MS SQL so you will have to be more specific in what you are looking for.

I would by a book today, and leave Books online open 24x7...

quote:
After ReReading Seventhnight's link it seems to me that I HAVE provided the DML that I have tried already.

Yes you did

quote:

What I need to accomplish is to either
1. Remove specific e-mail addresses from the VIEW MASTER_AVALON_Minus_Unsubscribe
or
2. Remove specific rows from the view MASTER_AVALON_Minus_Unsubscribe and the original table.

You do realize the a view is just that. It does not contain any data. Some views are not updateable or deleteable.

[quote]
I can manually remove the rows from the original tables using a standard delete command such as
delete from Avalon_Web_ContactUs where email = '###@###.###' but this does not affect the view MASTER_AVALON_Minus_Unsubscribe .

Well then I guess you got rid of data from a view that is not using that table.

[quote]
Please let me know EXACTLY what you need to know.

You need to no more about your database. Before you start knocking around, I would make sure I have ER Diagram of the database.

And I would hire a DBA.

You work in the NY Metro area?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 14:11:02

X002548:
Nobody working for me. Kind of a useless title. I have the code now. Thank you Tara for your help with that. I have enough SQL knowledge only to be dangerous. But I do backups nightly. I didn't realize until today that this was a view and what a view does. Sorry we are not in NY.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-21 : 14:32:29

Backups

The answer is, it depends.

Is this a transactional system. Are there users constantly banging away on this box?

Or is it more for reporting.

And how big is the database.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

brihaupt
Starting Member

10 Posts

Posted - 2005-06-21 : 14:50:41

I do a nightly full backup and hourly transaction log backups. This should cover it.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources