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 Development (2000)
 partitioned view delete issue

Author  Topic 

netedk
Starting Member

11 Posts

Posted - 2004-02-27 : 10:42:02
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

191 Posts

Posted - 2004-02-28 : 01:17:54
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 - 2004-03-01 : 08:21:36
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 - 2004-03-01 : 11:05:52
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

1 Post

Posted - 2012-04-11 : 03:14:21
It works!
Go to Top of Page
   

- Advertisement -