partitioned view delete issue
-
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
-
DK,
Please refer the following information from SQLServerCentral
http://sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=145
Good Hunting!AJ Ahrens
webmaster@kritter.net -
The view and the base tables fulfill all the requirements as mentioned in the FAQ. Also dropped and re-created the constraints (as suggested in the MS KB article) - but still I get that error - I get that error only on the specific query - for normal UPDATES/ DELETES I have no problems
any other suggestions??
Thanks
-
Can you try somthing like this:
DELETE VP
FROM dbo.vw_part VP
JOIN
( 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)
) VP2
WHERE VP.PK =VP2.PK
* Noel -
This too returns the same error. This is what I used:
DELETE VP
FROM dbo.vw_part VP
JOIN
( 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)
) VP2
ON VP.PK =VP2.PK
Any ideas what is happening here?
-
did you change the VP.PK = VP2.PK for some fields that uniquelly identify the records?
I put PK just so you replace those because I don't know what is the undelying primary key
* Noel -
Yeah, I did change the PK to reflect the actual primary keys. The primary key is a composite key of 3 fields. Here's what it looks like:
DELETE VP
FROM dbo.vw_part VP
JOIN
(
SELECT *
FROM dbo.vw_part
WHERE dbo.vw_part.partition_number = 1
AND EXISTS (
SELECT NULL
FROM dbo.vw_part b WITH (NOLOCK)
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)
)
) VP2
ON VP.partition_number = VP2.partition_number
AND VP.recipient_id = VP2.recipient_id
AND VP.user_id = VP2.user_id
-
How About:
DELETE dbo.vw_part
FROM
(
SELECT *
FROM dbo.vw_part
WHERE dbo.vw_part.partition_number = 1
AND EXISTS (
SELECT NULL
FROM dbo.vw_part b WITH (NOLOCK)
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)
)
) VP2
WHERE
dbo.vw_part.partition_number = VP2.partition_number
AND dbo.vw_part.recipient_id = VP2.recipient_id
AND dbo.vw_part.user_id = VP2.user_id
* Noel -
I havent tried this as yet. Will do in the next couple of minutes. Meanwhile I found this in BOL:
"DELETE statements are not allowed if there is a self-join with the same view or any of the member tables."
Looks like this could be the issue as there certainly is a self join here.
DK
-
Excellent Catch I didn't know that one!!!!
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply