SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 partitioned view delete issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

netedk
Starting Member

11 Posts

Posted - 02/27/2004 :  10:42:02  Show Profile  Visit netedk's Homepage  Reply with Quote
I have a view with foll. definition:

CREATE VIEW vw_part AS

SELECT * FROM part_1
UNION ALL
SELECT * FROM part_2


When I issue the foll. statement I get an error:
Server: Msg 4439, Level 16, State 6, Line 3
Partitioned view 'vw_part' is not updatable because the source query contains references to partition table '[part_1]'.

Statement:
DELETE
FROM dbo.vw_part
WHERE dbo.vw_part.partition_number = 1
AND EXISTS (
SELECT NULL
FROM dbo.vw_part b
WHERE
b.recipient_id = dbo.vw_part.recipient_id
AND b.message_id = dbo.vw_part.message_id
GROUP BY
b.recipient_id, b.message_id
HAVING
dbo.vw_part.fax_to_recipient_id < MAX(b.fax_to_recipient_id)
)


When I issue a similar SELECT statement, it executes without any issues:
SELECT *
FROM dbo.vw_part
WHERE dbo.vw_part.partition_number = 1
AND EXISTS (
SELECT NULL
FROM dbo.vw_part b
WHERE
b.recipient_id = dbo.vw_part.recipient_id
AND b.message_id = dbo.vw_part.message_id
GROUP BY
b.recipient_id, b.message_id
HAVING
dbo.vw_part.fax_to_recipient_id < MAX(b.fax_to_recipient_id)
)


I can execute a normal DELETE/ UPDATE/ INSERT statement on the view. But the above one fails.

Any help is greatly appreciated.

Thanks

DK

JohnDeere
Posting Yak Master

USA
191 Posts

Posted - 02/28/2004 :  01:17:54  Show Profile  Reply with Quote
I believe what you are trying to do is not allowed on a partitoned view.

DELETE Statements
DELETE statements remove data in one or more of the member tables through the partitioned view. The DELETE statements must adhere to this rule:

DELETE statements are not allowed if there is a self-join with the same view or any of the member tables.

I believe you are joining the vw_part view twice.

Lance Harra
Go to Top of Page

netedk
Starting Member

11 Posts

Posted - 03/01/2004 :  08:21:36  Show Profile  Visit netedk's Homepage  Reply with Quote
Thanks for the information. Now I know there's no use banging my head against this. But is there any other way I could achieve the same result?

Any ideas/ hints is greatly appreciated.

Thanks

DK
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/01/2004 :  11:05:52  Show Profile  Reply with Quote
Do deletes against the base tanles?

Also DON'T use SELECT * in your views...create the column list



Brett

8-)
Go to Top of Page

bloger7791
Starting Member

Russia
1 Posts

Posted - 04/11/2012 :  03:14:21  Show Profile  Reply with Quote
It works!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000