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.
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 ASSELECT * FROM part_1 UNION ALLSELECT * FROM part_2When I issue the foll. statement I get an error:Server: Msg 4439, Level 16, State 6, Line 3Partitioned view 'vw_part' is not updatable because the source query contains references to partition table '[part_1]'.Statement:DELETEFROM dbo.vw_partWHERE dbo.vw_part.partition_number = 1AND 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_partWHERE dbo.vw_part.partition_number = 1AND 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.ThanksDK |
|
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 StatementsDELETE 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 |
|
|
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.ThanksDK |
|
|
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 listBrett8-) |
|
|
bloger7791
Starting Member
1 Post |
Posted - 2012-04-11 : 03:14:21
|
It works! |
|
|
|
|
|