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.
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.
partitioned view delete issue
Posted - 02/27/2004 : 10:42:02
