'Why am I getting a "Violation of PRIMARY KEY constraint 'R268_pk'. Cannot insert duplicate key in object" when I'm updating existing records?
I have a stored procedure that updates records in a production view (WORKORDERS_EVW) from a reporting table (RPT.Utility_MasterRecords). Occasionally I get the following error "Msg 2627, Level 14, State 1, Procedure NVV268_UPDATE, Line 43 [Batch Start Line 2] Violation of PRIMARY KEY constraint 'R268_pk'. Cannot insert duplicate key in object 'CFAdmin.WORKORDER'. The duplicate key value is (16501271). The statement has been terminated." However the stored procedure is only updating preexisting records in the view, not inserting anything new. I'm trying to understand what the error is and how can I resolve it?
Store Procedure code:
```-----------------------------------------------------------------------------------------
--Updates CFAdmin.Task_EVW0 and CFAdmin.WorkOrder_EVW for Completed tasks
-----------------------------------------------------------------------------------------
--1. Update Workorders
--**The WOs need to be updated before the Tasks so we can use the task fields to filter the WOs.
WITH MRandTASK as
(SELECT MR.WOID,
MR.UtilityResponse,
MR.UtilityCompletedDate,
Task.CompletedDate
FROM RPT.Utility_MasterRecords as MR
INNER JOIN CFAdmin.Task_evw as Task
ON MR.TaskID = Task.OBJECTID) --need this CTE so we can filter WOs based on the task completed date
MERGE CFAdmin.Workorder_EVW AS T
USING MRandTASK AS S
ON T.ObjectID = S.WOID
WHEN MATCHED
AND S.UtilityResponse = 'Completed'
AND S.UtilityCompletedDate IS NOT NULL
AND S.CompletedDate IS NULL
THEN
UPDATE
SET
T.WOEntity = 0, --In-House
T.WireConflict = 5, --"All Conflicts Resolved",
T.Comments = ISNULL(T.Comments, '') + ' ' + '.Utility reported completed date:' + ' ' + CAST(S.UtilityCompletedDate as nvarchar(25));
--2. Update tasks
MERGE CFAdmin.Task_EVW AS T --Target
USING RPT.Utility_MasterRecords AS S --Source
ON T.ObjectID = S.TaskID --Unique; Work Order was not used since some Work Orders have two tasks
WHEN MATCHED
AND S.UtilityResponse = N'Completed'
AND S.UtilityCompletedDate IS NOT NULL
AND T.CompletedDate IS NULL
THEN
UPDATE
SET
T.CompletedDate = GetDate(),
T.UpdatedByName = 'ConEdison',
T.UpdatedDate = GetDate(); ```
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
