Error: The MERGE statement attempted to UPDATE or DELETE the same row more than once... When processing the Load Constituent task from BBDW_DIM_CONSTITUENT

When running an ETL refresh you may run into the following error when processing the 'Upsert Execute SQL Task' from the 'Load Constituent' portion of the BBDW_DIM_CONSTITUENT package:

Error: 2018-04-24 03:46:28.00
   Code: 0xC002F210
   Source: Upsert Execute SQL Task
   Description: Executing the query "declare @COUNTS table([ACTION] varchar(28), [INSER..." failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
Warning: 2018-04-24 03:46:28.00
   Code: 0x80019002
   Source: Load Constituent
 
This issue was caused by a data conversion problem.  If you are experiencing a similar issue, review  How to identify data conversion issues in Blackbaud CRM for more information.
 
  1. The following scripts should be run to identify the problematic data in the OLTP (Blackbaud CRM) database:
/* Script to check for Constituent's with multiple addresses marked as primary */
select CONSTITUENTID, count(*)
from ADDRESS 
where ISPRIMARY = 1
group by CONSTITUENTID
having count(*) > 1;
 
/* Script to check for Constituent's with multiple email addresses marked as primary */
select CONSTITUENTID, count(*)
from EMAILADDRESS 
where ISPRIMARY = 1
group by CONSTITUENTID
having count(*) > 1;

/* Script to check for Constituent's with multiple phones marked as primary */
select CONSTITUENTID, count(*)
from PHONE
where ISPRIMARY = 1
group by CONSTITUENTID
having count(*) > 1;
  1. If results are returned in any of the above scripts, the records included will need to be updated to correct the multiple primary address/email address/phone issue and the ETL will need to be reset and refreshed.
  2. If there are no results returned in the above scripts, the ETL will need to be reset and refreshed

Environment

 Blackbaud CRM

Was this article helpful?